DLookup Issue 1

[zinja]

I am trying to get a reference number from another table using a key value in a query. Right now, it is working, but is pulling the wrong ref_number. What I need to do is add another condition to the dlookup criteria to only get a ref_number of ref_type="ST"

Here is my SQL:

SELECT "60000006" AS [Authorization Number], IIf([ivh_consignee]="INTDOM","Domino","Pine Bluff") AS [Receiving Location], IIf([ivh_unit]="TON","TONS","FLAT") AS UOM, "Hines Trucking" AS Hauler, dbo_invoiceheader.ivh_order_cmd_code, dbo_invoiceheader.ivh_ref_number AS [Ticket Number], dbo_invoiceheader.ivh_invoicenumber, DLookUp("ref_number","referencenumber","ord_hdrnumber=" & [ord_hdrnumber]) AS [BOL#], dbo_invoiceheader.ord_hdrnumber
FROM dbo_invoiceheader
WHERE (((dbo_invoiceheader.ivh_mbnumber)=[forms]![frmEmailMB]![txtMBNumber]));

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[AlexCuse]

Can you not change your criteria in the dlookup to this (not tested):

"ord_hdrnumber=" & [ord_hdrnumber] & " AND ref_type = 'ST'")

If this does not work, you could set up a query that filters your table to only the rows with ref_type = 'ST', and do your dlookup from there.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[zinja]

Perfect!

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[AlexCuse]

Glad it worked

Ignorance of certain subjects is a great part of wisdom