dlookup using a date defiined by another dlookup

[Guest]

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")

 

[Guest]

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