DLookup() with Date criteria (1 Viewer)

John Big Booty

AWF VIP
Local time
Tomorrow, 01:42
Joined
Aug 29, 2005
Messages
8,263
Yep, that works. However it's still no closer to explaining why there are eleven dates per month that fail without formatting :confused:
 

vbaInet

AWF VIP
Local time
Today, 14:42
Joined
Jan 22, 2010
Messages
26,374
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

AWF VIP
Local time
Tomorrow, 01:42
Joined
Aug 29, 2005
Messages
8,263
...

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

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

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

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

Beetle

Duly Registered Boozer
Local time
Today, 08:42
Joined
Apr 30, 2011
Messages
1,808
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

AWF VIP
Local time
Tomorrow, 01:42
Joined
Aug 29, 2005
Messages
8,263
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

Registered User.
Local time
Tomorrow, 01:42
Joined
Apr 30, 2003
Messages
3,202
Simply change your regional settings for testing.

Code:
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.

Code:
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

AWF VIP
Local time
Today, 14:42
Joined
Jan 22, 2010
Messages
26,374
Clean your keyboard out lad, I take that as a mortal insult :D AUS :)


Yes I suspect the same :rolleyes: 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. :)
 

mossuvi

New member
Local time
Today, 07:42
Joined
Feb 24, 2016
Messages
1
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
 

Users who are viewing this thread

Top Bottom