Dlookup with Date criteria always return 0

[FL0XN0X]

Dear,

I have a Query with the name 'Finale Capaciteit' that holds 3 columns:
column1: Datum (contains a date)
column2: Totale Forecast VM (contains a number)
column3: Totale Forecast NM (contains a number)

This query is populated with data.

In the 'on click' event of a Command Button on a Form, I want to retrieve data form this Query, so my code in vba looks like:

Dim Datum As Date
Datum = DateSerial(Keuzelijst602.Value, 1, BeginOfMonthJan) ----> here I populate the variable Datum with the date that must be lookup'ed in the Query and return the corresponding value in column [Totale Forecast VM]"
Capacity = DLookup("[Totale Forecast VM]", "[Finale Capaciteit]", "[Finale Capaciteit].Datum=Datum")

Although the query contains data different from 0 for a given date, my Dlookup function ALWAYS returns 0 ....while the corresponding column [Totale Forecast VM] contains data <> 0

Is the code wrong?

Thank you in advance
Kind regards,
Bart

[CJ_London]

when using dates as string in sql you need to:

1. use the # char in order to tell sql that the text between them is to be treated as a date
2. sql only recognises the US format for dates of month/day/year
3. as written, your criteria is not built correctly, it is trying to compare your field with a value of 'Datum', not a date

so instead of

"[Finale Capaciteit].Datum=Datum"

try

"[Finale Capaciteit].Datum=#" & format(Datum,"mm/dd/yyyy") & "#"

[davegri]

Try this:

Capacity = DLookup("[Totale Forecast VM]", "[Finale Capaciteit]", "[Finale Capaciteit].Datum=" & Datum)

[FL0XN0X]

Ajax, just did the test and it works !!!!!!!!!!!!!!!!!

Thank you !!!!