DLookup() with Date criteria

[John Big Booty]

Yep, that works. However it's still no closer to explaining why there are eleven dates per month that fail without formatting

 

[vbaInet]

Sorry JBB, it's been a while I touched Access VBA but I think I had a problem similar to yours a while back and concluded that it was a problem with domain aggregate functions. It would appear that these functions interpret dates using their own pre-defined locale settings.

What's your locale settings? U.S.?

 

[John Big Booty]

...

What's your locale settings? U.S.?

Clean your keyboard out lad, I take that as a mortal insult AUS

...It would appear that these functions interpret dates using their own pre-defined locale settings.

...

Yes I suspect the same It would be interesting to hear from our American colleges on the subject.

 

[Beetle]

Yes I suspect the same It would be interesting to hear from our American colleges on the subject.

Your Dlookup appears to work fine here on the Yankee side, so it looks like you guys are right in that it must be something with how the domain functions interpret the locale settings for dates.

 

[John Big Booty]

Your Dlookup appears to work fine here on the Yankee side, so it looks like you guys are right in that it must be something with how the domain functions interpret the locale settings for dates.

Thanks Beetle. So the code I posted in Post #1 works straight up on the US set up? In which case I think the vbaInet's code in post #20 should be put in a handy location for future reference.

 

[ChrisO]

Simply change your regional settings for testing.

Private Sub Command3_Click()
        
    MsgBox "DupDate = #" & Me.RecDate & "#" & "   " & Format(Me.RecDate, "Medium Date")
        
    MsgBox Nz(DLookup("Note", "TBL_Dup", "DupDate = #" & Me.RecDate & "#"), "Nada")
        
End Sub

Regional Settings:-

English (Australia)
DupDate = #1/03/2012# 01-Mar-12

English (United States)
DupDate = #3/1/2012# 01-Mar-12

French (Switzerland)
DupDate = #01.03.2012# 01-mars-12
This crashes on the period

Notes:
1.
Formatting the Controls as Medium Date returns a 4 digit year.
Using Format(Me.RecDate, "Medium Date") only returns a 2 digit year.

2.
In all cases it is in Day Month Year format irrespective of regional settings. So, in the US where dates default to Month Day Year the Medium Date format still returns Day Month Year.

3.
The selection of the date format in the combo box list is sensitive to the words and date separator, as set in regional settings, but the Medium Date format is not sensitive to the General Date D/M/Y distribution in regional settings. It appears that it is either set by Access or Windows but I don’t know where.

4.
http://allenbrowne.com/ser-36.html

The above link supplies an explanation and a solution.

Private Sub Command3_Click()
        
    MsgBox "DupDate = #" & Me.RecDate & "#" & "   " & SQLDate(Me.RecDate)
        
    MsgBox Nz(DLookup("Note", "TBL_Dup", "DupDate = " & SQLDate(Me.RecDate)), "Nada")
        
End Sub

Chris.

 

[vbaInet]

Clean your keyboard out lad, I take that as a mortal insult AUS


Yes I suspect the same It would be interesting to hear from our American colleges on the subject.

Ah... now I never knew that. I've always thought you were Canadian. Don't know why.

 

[John Big Booty]

Canadian? Could be worse I guess

 

[mossuvi]

Can someone please assist me:

i am trying to use a dlookup function that seacrhes for a record with todays date..

eg:

Dim today As Date
today = Format(Now(), "mm / dd / yyyy")
If (Nz(DLookup("Incoming Customer", "EnquiryManagement", "datevalue(TodaysDate)= " & DateValue(Me.date) & " "))) > 0 Then
Else
End If

gives me syntax error..

please assist dear genius friends

 

[topdesk123]

Change datevalue(TodaysDate) to date()

HTH