IIF Statement, DLOOKUP in Query

[trishcollins]

I am using a form to determine if a user wants to include all locations, All HQs, Divisional HQs, or only the National HQ in their report. The pulldown in the form gives them those options. The database itself is imported and doesn't have a field to determine what type the location is. So, I am created a field in the query called "HQ_Status"m which determines the type based on the LOCATION CODE field.

HQ_Status: IIf([LOCATION CODE] In ("CON04773","CON06661","CON06886"),"NHQ",IIf([LOCATION CODE] In ("CON02414","CON02475","CON02513","CON04786","CON06771","CON06749","CON02854","CON03080","CON05766","CON04788","CON03816","CON04772","CON03817"),"DHQ","Detachments or Other Non-HQ Site"))

I have created a separate table (NSB Dynadocs HQs) which has a shortlist of the location codes (CON_Number) and added a field called Location_Type for identifying their type -- either DHQ and NHQ . By default, if they are not in that table, they are "Detachments or Other Non-HQ Site".

Rather than having to edit the expression in the query, should any of those codes change, I would like to do a dlookup in the HQ_Status expression that if it found the LOCATION CODE in the CON_Number field that other table, it would return the contents of the Location_Type field (DHQ or NHQ) from that table. If not found, it would put "Detachments or Other Non-HQ Site" as the HQ_Status. This information is then used in the Criteria field to determine if it should be included in the report.

Basically, what I am looking for is:

HQ_Status:= iif Dlookup [NSB Dynadocs HQs][CON_Number] = [NSB Dynadocs Inventory][LOCATION CODE], return [NSB Dynadocs HQs][Location_Type], otherwise "Detachments or Other Non-HQ Site"

I am stuck on the syntax. Can anyone help?

Trish

 

[bwellbor]

Let's see if I understand. You have a table called "NSB Dynadocs HQs" with fields [CON_Number] and [Location_Type]. You also have a table called "NSB Dynadocs Inventory" with field [LOCATION CODE]. When [CON_Number] and [LOCATION CODE] match up, show the appropriate [Location_Type], else show "Detachments or Other Non-HQ Site". Is that on track? If so, try this. Create a Select query and only add the "NSB Dynadocs Inventory" table. One of the fields can be your [LOCATION CODE] field. Then put this expression into your next field:

HQ_Status: Nz(DLookUp("[Location_Type]","NSB Dynadocs HQs","[CON_Number] = '" & [LOCATION CODE] & "'"),"Detachments or Other Non-HQ Site")

Let me know how that goes or if I'm off base.

 

[trishcollins]

Thanks! That's EXACTLY what I am trying to do. I just copied your statement and it worked perfectly!

Cheers, Trish