DLookup() with Date criteria

John Big Booty

AWF VIP
Local time
, 10:39
Joined
Aug 29, 2005
Messages
8,262
I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate :banghead:

Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things :confused:
 
Hi JBB :)

Format both ends of your date criterion and see where that gets you. If that works, then only format the rhs.
 
Thanks for the suggestion, I'll give it a whirl :)
 
Format with date on the rhs alone doesn't seem to work, and I'm having trouble with the syntax for the lhs :o

However it seems that the twelfth of the month is the cusp, the thirteenth and on seems to work fine but any date between the first and twelfth fails :confused:
 
That is an indication that the dates are messed up. Access knows that there cannot be 13 months, so gets dates of day 13 ++ right. There is some implict conversion from date to string or viceversa going on. If you are not on US locale, then make them explicit, wherever you meet them.
 
Something like this:
Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND Format(ExchDate, ""mmddyyyy"") = " & Format(Me.ShipOBDate, "mmddyyyy"))) And Me.Combo4 <> 2 Then
 
Thanks guys, a couple of things to have a play with. I won't get a chance today but will try tomorrow :)
 
Was the problem caused because the dates include time? If so, use DateValue() to extract only the date.

Where DateValue(somedate) = DateValue(someotherdate)
 
No there was no time component involved. The problem involved matching dates that fell between the first and twelfth of the month ie. it failed :rolleyes:

It didn't seem to matter if the dates where being transferred via the OpenArgs or even directly from table to table via the Append Query. vbaInet's solution seems to have solved my problem :)

I still have a few other tweaks to make to the DB so I'll see how the rest of it goes.
 
Happy to hear JBB :)

It's very strange that it's failing on some dates.
 
I was thinking the same but my concern was how it happened! Maybe this code still exist somewhere. Hmmm...
 
I've made a very simple DB to demonstrate the problem.

The Dlookup() will not work for dates less than the thirteenth unless the date and the month number are the same. No problem for dates from the thirteenth to the end of the month.

The interesting thing is that for both tables I've formatted the dates as Medium Date, and for all intents and purposes the dates appear the same, until you try and match them using DLookUp(). Now if you try and match the dates in a query; No Problem :eek:

So I'm wondering if it's getting messed up by the DLookup() function, but I just can't see why :banghead:
 

Attachments

Give me a DLookup() that fails and I will test it here.
 
There's only two tables, the one with the original dates and the one with the duplicates. As I said very simple :)
 
The Show Related Note button, on FRM_OrgDate is the one with the Dlookup() behind it.
 
Ok, this one will always work.
Code:
MsgBox Nz(DLookup("Note", "TBL_Dup", "DupDate = " & Format(Me.RecDate, "\#mm/dd/yyyy\#")), "Nada")
 

Users who are viewing this thread

Back
Top Bottom