Using multiple criteria to lookup values in related records

[dlevene]

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!

[Stewart Ross]

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.

=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:

rev_address1: IIF(IsNull([Address 1]), [admin address 1], [Address 1])
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

[dlevene]

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!