DLookup Error in Form (1 Viewer)

djcbooks

New member
Local time
Today, 03:47
Joined
Mar 10, 2017
Messages
2
I am trying to lookup Conversion Rates based on a user-entered Invoice Date in an Access 2016 form. Here is a sample of the Rate Table:

General - Daily FX Rate History
Rate_Date Effective_Date USD-CAD CAD-USD
3/1/2017 3/2/2017 1.3335 0.7499
3/2/2017 3/3/2017 1.3399 0.7463
3/3/2017 3/6/2017 1.3379 0.7474
3/6/2017 3/7/2017 1.341 0.7457
3/7/2017 3/8/2017 1.3416 0.7454
3/8/2017 3/9/2017 1.3494 0.7411

From the Invoice Entry form, when the user enters the Invoice Date, the base_fx_rate control should look up the Invoice Date and match it to the corresponding Effective_date in the "General - Daily FX Rate History" table and return the USD-CAD value.

Here is the statement for the Control Source: =IIf(IsNull([invoice_date]),0,DLookUp("[USD-CAD]"," [General - Daily FX Rate History]","[Effective_Date] = #" & [Forms]![frm_AddInvoice]![invoice_date] & "#"))

When I first go into the form, the value in base_fx_rate is 0 as expected, but after entering a date value (I've been testing with 3/1/2017) in the invoice_date field, the base_fx_rate value changes to #Error.

I have searched for solutions, but not finding anything that helps in resolving this. Please let me know if you can help. Thanks!
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 10:47
Joined
Feb 19, 2013
Messages
16,553
I can't read your data, but your dlookup seems to have a space before the square bracket for the table name and the table name has special characters (-) which can cause problems.

moving on to referencing your form control, assuming the dlookup is in the same form you just need [invoice_date], and the date needs to be in US format (looks like you are in US so should not be a problem) of 'mm/dd/yyyy'. - 3/1/2017 will be interpreted as 1st March

Finally, if a matching date is not found, dlookup will return a null
 

djcbooks

New member
Local time
Today, 03:47
Joined
Mar 10, 2017
Messages
2
OMG - it was the space in front of my table name causing the issue!!!

Thanks so much for the quick reply and eagle eyes!!
 

Users who are viewing this thread

Top Bottom