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.
7  3200 
You wouldn't put it in the criteria, you'd put it in the Field.
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])
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?
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
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.
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!
Yes, it works, thanks so much. You have saved me so much time. Thanks again!
Happy to help.
J
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John Baker |
last post by:
Hi:
As those who have looked at this newsgroup recently will realize, I am a neophyte with
Access, although I have experienced with Approach (the Lotus product). There are things I
could easily...
|
by: LesM |
last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002
SP3.
I have Progress table that is linked via ODBC into Access using OpenLink
Lite for Progress 9.0b. For over a year, using...
|
by: Neil |
last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is
upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server
at the same time. We're moving to SQL Server...
|
by: rguarnieri |
last post by:
Hi!
I'm trying to create a query with a boolean expression like this:
select (4 and 1) as Value from Table1
this query return always -1, but when I make the same calculation in
visual...
|
by: Alan Mailer |
last post by:
Ok, my Access 2002 language writing skills are VERY rusty,. I would
know how to do what I need using SQL Server's "Coalesce' function, but
I don't have that available to me in the Access 2002...
|
by: Sheldon |
last post by:
I have the following query expression -
Like Format((!!)) & "/*/" & (!
!)
which would translate to e.g. 04/*/2007 if someone is running a report
for last month. The above expression is part of a...
|
by: deanoooo812 |
last post by:
I have an Access query (written in MS Access 2000 - thats all we've got - don't get me started on that topic...) for making pharmacy dispensing labels based on an extract from an automated dispensing...
|
by: MMcCarthy |
last post by:
Access has a number of built-in functions which can be generally used in queries or VBA code. Some of the more common ones are:
Note: anything in square brackets is optional
Date Functions
...
|
by: magickarle |
last post by:
Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.
IE: something simple:
Select EmplID from empl_Lst
where empl_lst.timestamp between...
|
by: ollyb303 |
last post by:
Hello,
I have been using the following expression in Access as part of a statement to query an Oracle database:
(Sum(CASE WHEN STATS_DAILY_SA.LOGIN_TIME > (STATS_DAILY_SA.SCHEDULED_TIME -...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
