0

I am trying to create an expression in Access 2016 to return a value from another table when a key is matched from the current table. I am sure it is syntax related, but am at a loss....

I want to add the receipt date (in tbl_RECEIPTS) as a column in tbl_POs based on the key value from the two tables being equal. INNER JOIN doesn't work because if we have not received the item, the field returned by DLookup should be blank.

Here is what I have: The key is a string value in both tables.

DLookUp('[DATERECEIVED]',"tbl_RECEIPTS"," '[tbl_POs].[KeyVal]' = '[tbl_RECEIPTS].[KeyVal]' ")

The query runs, but returns a blank value for every record.

Any help would be GREATLY appreciated!

RAReed
  • 101

2 Answers2

0

It could be:

DLookUp("[DATERECEIVED]","tbl_RECEIPTS","[KeyVal] = '" & [tbl_POs].[KeyVal] & "'")
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I appreciate the answer, but it doesnt seem to work. It brings up a dialog box asking for a value for [tbl_POs].[KeyVal]. That value should be provided by the table. – RAReed Feb 12 '19 at 16:26
  • Maybe DLookup is not what I should be using. I am new to Access and sql queries – RAReed Feb 12 '19 at 16:27
0

I found it!

Sorry to answer my own question but What worked was using a LEFT JOIN with the tables.

The result will return blanks in the new date field when no corresponding key value is found.

RAReed
  • 101