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

Access Query Expression Help

P: 8
Help, Short story is I have 2 tables. 1 for the Companies and 1 for the Employees. These are used in a Main form where the user enters the Company info with a sub form for the employees info. (1 to Many). In the Employees table which is used for the subform in the main there is an extra field (Yes/No Check box) to flag if the address is an alternate address or not and the alternate address is entered in the subform. These 2 tables produce a query for mailing letters(reports). The problem is that I can't use all the address fields from the Companies table in my mailing letters query because some of the employees have alternate addresses other than the company addresses. So what I am trying to do is create an expression or something, somewhere, where in the Mailing Query, IFF the check box field is FALSE int he Employees table then use the Companies Table address info, ELSE use the Employee Table address info. Following is something I was playing with in the mailing list query criteria.
IIF([Employee Names Table]![Is_Alt_Addr]=FALSE,[Companies Table]![BillAddress1],[Employee Names Table]![Address1])
Maybe I am not going in the right direction so if anyone has an idea my ears are open. Thanks for your time.
May 7 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,359
You wouldn't put it in the criteria, you'd put it in the Field.
May 7 '07 #2

JConsulting
Expert 100+
P: 603
Help, Short story is I have 2 tables. 1 for the Companies and 1 for the Employees. These are used in a Main form where the user enters the Company info with a sub form for the employees info. (1 to Many). In the Employees table which is used for the subform in the main there is an extra field (Yes/No Check box) to flag if the address is an alternate address or not and the alternate address is entered in the subform. These 2 tables produce a query for mailing letters(reports). The problem is that I can't use all the address fields from the Companies table in my mailing letters query because some of the employees have alternate addresses other than the company addresses. So what I am trying to do is create an expression or something, somewhere, where in the Mailing Query, IFF the check box field is FALSE int he Employees table then use the Companies Table address info, ELSE use the Employee Table address info. Following is something I was playing with in the mailing list query criteria.
IIF([Employee Names Table]![Is_Alt_Addr]=FALSE,[Companies Table]![BillAddress1],[Employee Names Table]![Address1])
Maybe I am not going in the right direction so if anyone has an idea my ears are open. Thanks for your time.
Your logic is sound...where are you trying to apply the criteria?

Try creating an "alias" for that iif statement (which equates to a new field in your query)

MyAdress: IIF([Employee Names Table]![Is_Alt_Addr]=FALSE,[Companies Table]![BillAddress1],[Employee Names Table]![Address1])
May 7 '07 #3

P: 8
You wouldn't put it in the criteria, you'd put it in the Field.
Thanks for the reply. So I would have to create an "Alias" field in the query with the expression or put the expression in the field on the mailing forms? That would mean having an expression for every address field in every form?
May 8 '07 #4

JConsulting
Expert 100+
P: 603
Thanks for the reply. So I would have to create an "Alias" field in the query with the expression or put the expression in the field on the mailing forms? That would mean having an expression for every address field in every form?

The alias field would become part of the query, which I assume is the recordsource for your forms. You can then add it as a field and refer to it by name on your forms.
J
May 8 '07 #5

P: 8
The alias field would become part of the query, which I assume is the recordsource for your forms. You can then add it as a field and refer to it by name on your forms.
J
Thanks, I will give it a try.
May 8 '07 #6

P: 8
The alias field would become part of the query, which I assume is the recordsource for your forms. You can then add it as a field and refer to it by name on your forms.
J
Yes, it works, thanks so much. You have saved me so much time. Thanks again!
May 8 '07 #7

JConsulting
Expert 100+
P: 603
Yes, it works, thanks so much. You have saved me so much time. Thanks again!
Happy to help.
J
May 8 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.