Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 5 votes, 5.00 average. Display Modes
Old 07-03-2012, 02:55 AM   #16
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

I'll be interested in your thoughts, once you've had a look at the sample

__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-03-2012, 03:00 AM   #17
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: DLookup() with Date criteria

Lol ok. What table should I focus on?
vbaInet is offline   Reply With Quote
Old 07-03-2012, 03:17 AM   #18
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

There's only two tables, the one with the original dates and the one with the duplicates. As I said very simple

__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-03-2012, 03:21 AM   #19
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

The Show Related Note button, on FRM_OrgDate is the one with the Dlookup() behind it.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-03-2012, 03:30 AM   #20
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: DLookup() with Date criteria

Ok, this one will always work.
Code:
MsgBox Nz(DLookup("Note", "TBL_Dup", "DupDate = " & Format(Me.RecDate, "\#mm/dd/yyyy\#")), "Nada")
vbaInet is offline   Reply With Quote
Old 07-03-2012, 03:36 AM   #21
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

Yep, that works. However it's still no closer to explaining why there are eleven dates per month that fail without formatting
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-03-2012, 05:10 AM   #22
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: DLookup() with Date criteria

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.?

vbaInet is offline   Reply With Quote
Old 07-03-2012, 01:59 PM   #23
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

Quote:
Originally Posted by vbaInet View Post
...

What's your locale settings? U.S.?
Clean your keyboard out lad, I take that as a mortal insult AUS

Quote:
Originally Posted by vbaInet View Post
...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.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-03-2012, 02:37 PM   #24
Beetle
Duly Registered Boozer
 
Join Date: Apr 2011
Location: Camp Swampy (Denver, CO)
Posts: 1,806
Thanks: 13
Thanked 458 Times in 449 Posts
Beetle will become famous soon enough Beetle will become famous soon enough
Re: DLookup() with Date criteria

Quote:
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.
__________________
Sean Bailey

Dim Thirsty As String
Thirsty = "Insert Into Glass (Select Beer From Fridge)"
DoCmd.Execute Thirsty, dbPourAnotherOnError
Beetle is offline   Reply With Quote
The Following User Says Thank You to Beetle For This Useful Post:
John Big Booty (07-03-2012)
Old 07-03-2012, 03:18 PM   #25
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

Quote:
Originally Posted by Beetle View Post
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.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 07-03-2012, 04:25 PM   #26
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 275 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
Re: DLookup() with Date criteria

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.
__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ChrisO is offline   Reply With Quote
The Following User Says Thank You to ChrisO For This Useful Post:
John Big Booty (07-03-2012)
Old 07-04-2012, 12:21 AM   #27
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: DLookup() with Date criteria

Quote:
Originally Posted by John Big Booty View Post
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.
vbaInet is offline   Reply With Quote
Old 07-04-2012, 01:07 AM   #28
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: DLookup() with Date criteria

Canadian? Could be worse I guess
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 02-24-2016, 03:55 AM   #29
mossuvi
Newly Registered User
 
Join Date: Feb 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
mossuvi is on a distinguished road
Re: DLookup() with Date criteria

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
mossuvi is offline   Reply With Quote
Old 02-24-2016, 04:44 AM   #30
topdesk123
Newly Registered User
 
Join Date: Mar 2013
Posts: 42
Thanks: 5
Thanked 1 Time in 1 Post
topdesk123 is on a distinguished road
Re: DLookup() with Date criteria

Change datevalue(TodaysDate) to date()

HTH

topdesk123 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookUp with multi criteria including between date range robsworld78 Reports 0 06-06-2011 09:51 PM
[Question] Dlookup date criteria that is null sunset1215 Modules & VBA 1 05-05-2011 06:14 PM
Help With Dlookup Criteria prashant2002 General 12 09-10-2010 08:02 AM
DLOOKUP with multiple date criteria one of which is function kdwoell Modules & VBA 2 08-23-2008 05:23 AM
Dlookup using Date as criteria sambo Modules & VBA 1 09-16-2002 02:47 PM




All times are GMT -8. The time now is 11:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World