dlookup using a date defiined by another dlookup

B

BennyDHill

Hi, i am trying to check whether a date that exists in one table, already
exists in another before adding data to that table. i cannot use referential
integrity due to the way the data is set out. i am getting the date for the
first dlookup, but the second one is returning the wrong date or error
messages. i have tried different criteria such as [payment date] =
FECVCollected, [Payment Date], "FECCollected" etc. any ideas?

regards, Ben

Dim FECCollected As Date
Dim FECValue As Variant

FECCollected = DLookup("[Payment Date]", "tbl_Exchange_Rate_Data_Collected",
"[Lookup] ='1'")

FECValue = DLookup("[Payment Date]", "tbl_Exchange_Rate_Data", "[Payment
Date] = ""FECCollected")
 
S

SteveM

Try this:

Dim FECCollected As Date
Dim FECValue As Date

FECCollected = DLookup("[Payment Date]", "tbl_Exchange_Rate_Data_Collected",
"[Lookup] = " & 1)

FECValue = DLookup("[Payment Date]", "tbl_Exchange_Rate_Data", "[Payment
Date] = #" & FECCollected & "#")

Numeric values do not need quotes.
Text values must have single quotes.
Date values require octothorpes.

Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Group Footer DLookUp 0
DlookUp Help 6
Question on DLookup Syntax 1
Using Min Function within DLookup Function 0
DLookup 8
Date coming is 12/31/1899 7
DLookup Date 22
Dlookup 7

Top