DLookup for dates

Andrew Thorpe

Registered User.
Local time
Today, 18:46
Joined
Apr 18, 2009
Messages
59
In a table (HOLIDAY), I have noted some dates as being official holidays. Within a form, I am trying to use the DLookup function to see if the date I am using is recorded as being a Holiday.
My line is:
If Forms!frmShift!StartDate = DLookup("HolDate", "HOLIDAY", "HolDate = Forms!frmShift!StartDate") Then.....
Unfortunately, it doesn't work. I have tried using # signs on each side of the date, but no joy. Would appreciate any help anyone can offer. Thank you. Andrew
 
DLookup will return a "You canceled the previous operation" if it returns a NULL.

So, instead, use DCOUNT -

If DCount("*", "HOLIDAY", "HolDate = #" & Forms!frmShift!StartDate & "#") > 0 Then
 
Bob. Thank you so much. It works perfectly. I would never ever have found that solution on my own. Thank you. Andrew
 
It seems very strange, but sometimes the function works, but then with another date - already in the Holiday table - it doesn't. I've tried using the DCount and DLookup functions, and the results are nearly always the same. My code is as follows:
Private Sub StartDate_AfterUpdate()
'Use DCount function to test for starting on a holiday
If DCount("*", "HOLIDAY", "HolDate = #" & Forms!frmShift!StartDate & "#") > 0 Then
StartsOnHol = True
Else
StartsOnHol = False
End If

'Use DLookup function to test for starting on a holiday
If Forms!frmShift!StartDate = DLookup("[HolDate]", "HOLIDAY", "HolDate = #" & Forms!frmShift!StartDate & "#") Then
HolStartonDLookUp = True
Else
HolStartonDLookUp = False
End If
End Sub

I can't figure out why it works with some dates, and not others. I thought it might be mixing US and UK date formats, but I've used the calendar to avoid that.
 
Are you sure that HolDate has the date/time datatype in the table?
 
Thanks for your message. Yes, I've checked datatype. Making sure that I have the same in the form and in the table, I tried using the dd mmm yy format and then tried the built-in Medium Date. Curiously, with one exception so far, I have found that when the date has two digits eg 23rd of the month, it works. When it has only one, it doesn't, except when I used 1 January. I will try using the Int(....) wrapper as I understand that a day is actually stored as a number with lots of decimal places.
 
The format that needs to be used is the US date format (mm/dd/yyyy) when searching.
 
OK thanks. I'll try the mm/dd/yyyy format. (My idea of using the Int(....) didn't make any difference).
Just tried mm/dd/yyyy, but same result.
I have written a tiny new Db just to try this. 2 tables:
HOLIDAY with 2 fields HolDate (DateTime) and HolDescrip (Text 25).
SHIFT with ShiftID (AutoNumber), StartDate (DateTime) and StartOnHol (Yes/No).
I've tried it in Access 2000 and Access 2007 - same results. Sometimes the StartOnHol correctly turns to True when it should, and sometimes it doesn't. If the date is not on the list, it always correctly remains false.
 
Last edited:
Yes, definitely. I will zip it first. Can you give me an email address or can I send it through this forum?
 
If it is 2Mb or less when you zip it (remember to run Compact and Repair first before zipping) you can upload it here.
 
Hopefully the file is attached. I'm most grateful for your time and patience in looking at this for me. The file is called ShiftWork.
 

Attachments

Andrew:

Change your code to this:
Code:
Private Sub StartDate_AfterUpdate()
'Use DCount function to test for starting on a holiday
   If DCount("*", "HOLIDAY", "HolDate = #" & Format(Nz(Forms!frmShift!StartDate, 0), "Short Date") & "#") > 0 Then
      StartsOnHol = True
   Else
      StartsOnHol = False
   End If

'Use DLookup function to test for starting on a holiday
   If Forms!frmShift!StartDate = DLookup("[HolDate]", "HOLIDAY", "HolDate = #" & Format(Nz(Forms!frmShift!StartDate, 0), "Short Date") & "#") Then
      HolStartonDLookUp = True
   Else
      HolStartonDLookUp = False
   End If
End Sub

And also add this in:
Code:
Private Sub Form_Current()
   StartDate_AfterUpdate
End Sub

That way it calls the procedure when you move between records. I'm guessing that is why you see the inconsistencies.
 
OK. Many thanks. I'll give it a try.
........
I've tried it, but I'm afraid it still doesn't always work. In my HOLIDAY table, I have 7 April and 27 April. If I input 27 April and press tab, both checkboxes become True. If I enter 7 April, they remain False.
It's so strange, because the logic seems quite straighforward.
 
Last edited:
Strange because it works for me. Must be a regional date thing.
 
That could be it. Good to hear that it works OK on your system. I'm on Windows 7, but I have an old laptop on XP, so I'll try it on that. Thank you so much for your help and perseverance; very much appreciated. Andrew.
 
Bob. I finally got there. With USA settings it worked OK. With UK settings, I had to change to mm/dd/yyyy. Thanks very much for all your help. What a great forum; I'll be back!
 

Users who are viewing this thread

Back
Top Bottom