By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Queries - using the AS clause with functions, field names

P: n/a
I have created a simple select query (bound to a form in Access 2K)
that calls my own public function and returns a result to an
existing field in a table. I use the AS clause to reference the
existing field name using the same field name in the AS clause. This
works great, but I have never heard of the As clause being used like
this. Will I be in trouble doing it this way? Would it work in
SQLServer too? See my code for clarification. (shortened version)

Keep in mind "EndDate" is also the actual field name in my source
table, my GetEndDate function returns its value to the EndDate field
in the table perfectly.

Select [tbl_Contracts].[CustomerID], [tbl_Contracts].[ContractID],
IIf(IsNull([tbl_Contracts]![TermOfAgreement]) Or
IsNull([tbl_Contracts]![StartDate]),"",GetEndDate([tbl_Contracts]![StartDate],[tbl_Contracts]![TermOfAgreement]))
AS EndDate
FROM tbl_Contracts;

Thanks
William Roberts
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
What you have done is perfectly legal. If you look at the SQL for any query that
contains one or mor calculated fields, you will see the As clause for the
calculated field. Access assigns the field name Expr(n) by default if ou haven't
named the filed yourself.
--
PC Datasheet
A Resource for Access, Excel and Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Design and basic development for new applications
Additions, Modifications and "Fixes" for existing applications
Mentoring for do-it-yourselfers who want guidance
Complete application design and development
Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

"William Roberts" <wi*********@hotmail.com> wrote in message
news:d6**************************@posting.google.c om...
I have created a simple select query (bound to a form in Access 2K)
that calls my own public function and returns a result to an
existing field in a table. I use the AS clause to reference the
existing field name using the same field name in the AS clause. This
works great, but I have never heard of the As clause being used like
this. Will I be in trouble doing it this way? Would it work in
SQLServer too? See my code for clarification. (shortened version)

Keep in mind "EndDate" is also the actual field name in my source
table, my GetEndDate function returns its value to the EndDate field
in the table perfectly.

Select [tbl_Contracts].[CustomerID], [tbl_Contracts].[ContractID],
IIf(IsNull([tbl_Contracts]![TermOfAgreement]) Or
IsNull([tbl_Contracts]![StartDate]),"",GetEndDate([tbl_Contracts]![StartDate],[t
bl_Contracts]![TermOfAgreement])) AS EndDate
FROM tbl_Contracts;

Thanks
William Roberts

Nov 12 '05 #2

P: n/a
Thankyou very much.
William Roberts

"PC Datasheet" <re******@pcdatasheet.com> wrote in message news:<ch*****************@newsread1.news.atl.earth link.net>...
What you have done is perfectly legal. If you look at the SQL for any query that
contains one or mor calculated fields, you will see the As clause for the
calculated field. Access assigns the field name Expr(n) by default if ou haven't
named the filed yourself.
--
PC Datasheet
A Resource for Access, Excel and Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Design and basic development for new applications
Additions, Modifications and "Fixes" for existing applications
Mentoring for do-it-yourselfers who want guidance
Complete application design and development
Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

"William Roberts" <wi*********@hotmail.com> wrote in message
news:d6**************************@posting.google.c om...
I have created a simple select query (bound to a form in Access 2K)
that calls my own public function and returns a result to an
existing field in a table. I use the AS clause to reference the
existing field name using the same field name in the AS clause. This
works great, but I have never heard of the As clause being used like
this. Will I be in trouble doing it this way? Would it work in
SQLServer too? See my code for clarification. (shortened version)

Keep in mind "EndDate" is also the actual field name in my source
table, my GetEndDate function returns its value to the EndDate field
in the table perfectly.

Select [tbl_Contracts].[CustomerID], [tbl_Contracts].[ContractID],
IIf(IsNull([tbl_Contracts]![TermOfAgreement]) Or

IsNull([tbl_Contracts]![StartDate]),"",GetEndDate([tbl_Contracts]![StartDate],[t
bl_Contracts]![TermOfAgreement]))
AS EndDate
FROM tbl_Contracts;

Thanks
William Roberts

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.