dlookup and date (1 Viewer)

[Mister-B]

Hi there,

I have a split database. On the backend there is a linked table with all the dates of the public holidays occuring in the next few years. The table is called "ArbFreieTage" and the column with the dates is called "Datum". When I open the form "Außenpforte" I would like to check if the date in the field "Datum" on the form is in the table contained int the column. If it is I would like a message box to be displayed, if not then nothing is to happen. I have tried all variations of the code but the message box occurs every time even if the date is not in the table:

If DLookup("[ArbFreieTage]", "Datum", "[Datum] = #" & Forms!Außenpforte!Datum) & "#" Then MsgBox "Public Holiday"

What am I doing wrong?

 

[June7]

You have arguments mixed up. Also, second # delimiter is not within the function.

If DLookup("Datum", "[ArbFreieTage]", "[Datum] = #" & Forms!Außenpforte!Datum & "#") Then MsgBox "Public Holiday"

 

[Mister-B]

Thanks for the quick reply. Unfortunately the problem remains. The message box always appears when cycling through the records even if the date in the form field is not in the public holiday table.

 

[Gasman]

Is DLookUp() in that statement ever going to result in True? Don't you need to compare it to the form control again?
I would use DCount() ?

 

[sonic8]

What am I doing wrong?

The date in the where condition is not formatted correctly for Access SQL.
See my tutorial on SQL in VBA for more information. (Also available in German)

 

[June7]

Gasman, I tested. If there is no match, DLookup returns Null and is treated as False. If date is retrieved it evaluates as True. However, just to remove ambiguity, try:

If IsDate(DLookup(...)) Then

If that still doesn't work correctly then the issue is with date structure in the WHERE CONDITION. International dates do complicate. Review Microsoft Access tips: International Dates in Access (allenbrowne.com)

 

[Gasman]

Gasman, I tested. If there is no match, DLookup returns Null and is treated as False. If date is retrieved it evaluates as True. However, just to remove ambiguity, try:

If IsDate(DLookup(...)) Then

If that still doesn't work correctly then the issue is with date structure in the WHERE CONDITION. International dates do complicate. Review Microsoft Access tips: International Dates in Access (allenbrowne.com)

Hmm,
Thank you June.
As much as I like to abbreviate code, I think I will stick with more obvious comparison.