Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DLookup Issue 1

Status
Not open for further replies.

zinja

MIS
Nov 14, 2002
149
US
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:

Code:
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.
 
Can you not change your criteria in the dlookup to this (not tested):

Code:
"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
 
Perfect!

LJ Wilson

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

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top