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

Using multiple criteria to lookup values in related records

P: 4
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where [Contact Type] = "Community". [Contact Type] is a combo-box field with the values coming from a seperate table.

Problem is, some of these don't have addresses (ie. [Address 1], [Address 2] and [Post Code] are null). So I want to use the address of the Administrator for that community. This information is in records where [Contact Type] = "Administrator" and [Community] = whatever the community with the blank address is. [Community] is also a combo-box based on another table.

To do this, I wrote the following DLookUp for the first line of the address that I will place within an Nz() or If(IsNull()) expression when it's working:

=DLookUp("[Address 1]","[Main]","[Community] = " & Reports!Community!Community And "[Contact Type] = 'Administrator'")

However, this only gives the first line of the address of the first record in the table. When tested in the Immediate pane in VB I get "Type mismatch". If I test the same expression with only the first criteria I get "You canceled the previous operation", with only the second criteria I get "Data type mismatch". But if I test the criteria by themselves (seperately) they seem to work.

In summary, I seem to be getting the syntax for multiple text criteria of a DLookUp expression wrong .

Anyone got any ideas? I know the best thing would be a Recordset but I havent got a clue how to do them. Sorry for the long post, hope it makes sense, and thanks very much in advance!
Aug 12 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi, and welcome to Bytes.

For the immediate problem with your DLookup syntax, here is a revised version which corrects the problem with your AND clause.

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[Address 1]","[Main]","[Community] = " & Reports!Community!Community & " And [Contact Type] = 'Administrator'")
However, as you say this only returns one line of the address -so you will need to repeat it for the other lines.

I would instead use a query in which you join the base table to the table 'Main' storing the community contact details. You would still need to select the community administrator address if the other address is null, but this can be done using IIF statements or similar in the revised query to return one or other address line, like this:
Expand|Select|Wrap|Line Numbers
  1. rev_address1: IIF(IsNull([Address 1]), [admin address 1], [Address 1])
  2. rev_address2: IIF(IsNull([Address 1]), [admin address 2], [Address 2])
The test in each case is for null in the first line of the address - later lines may be intentionally null (if the address is short).

When I do this sort of thing myself (in selecting between work and home addresses, for instance) I use a custom function in VBA to return the correct address line to the underlying query concerned. It is more efficient by far than using IIFs, but I think at present you will find the IIFs much easier to implement.

-Stewart
Aug 12 '08 #2

P: 4
Thanks for that - turned out to be a combination of a wrong AND clause as you said, as well as the fact that both dropdown fields were being indexed by their AutoNumber ID rather than the option itself. Cheers!
Aug 12 '08 #3

Post your reply

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