470,648 Members | 1,608 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,648 developers. It's quick & easy.

Using multiple criteria to lookup values in related records

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
2 5286
Stewart Ross
2,545 Expert Mod 2GB
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.

Aug 12 '08 #2
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.

Similar topics

11 posts views Thread by dskillingstad | last post: by
8 posts views Thread by Christine Henderson | last post: by
4 posts views Thread by Robert Bravery | last post: by
2 posts views Thread by Mark Roughton | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.