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

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

"BennyDHill" wrote:

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