DLookup() with Date criteria

[John Big Booty]

I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;

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

 

[vbaInet]

Hi JBB

Format both ends of your date criterion and see where that gets you. If that works, then only format the rhs.

 

[John Big Booty]

Thanks for the suggestion, I'll give it a whirl

 

[John Big Booty]

Format with date on the rhs alone doesn't seem to work, and I'm having trouble with the syntax for the lhs

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

 

[spikepl]

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.

 

[vbaInet]

Something like this:

If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND Format(ExchDate, ""mmddyyyy"") = " & Format(Me.ShipOBDate, "mmddyyyy"))) And Me.Combo4 <> 2 Then

 

[John Big Booty]

Thanks guys, a couple of things to have a play with. I won't get a chance today but will try tomorrow

 

[John Big Booty]

Thanks vbaInet, that's done the trick

 

[Pat Hartman]

Was the problem caused because the dates include time? If so, use DateValue() to extract only the date.

Where DateValue(somedate) = DateValue(someotherdate)

 

[John Big Booty]

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

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.

 

[vbaInet]

Happy to hear JBB

It's very strange that it's failing on some dates.

 

[John Big Booty]

I think spikepl got it in post #5

 

[vbaInet]

I was thinking the same but my concern was how it happened! Maybe this code still exist somewhere. Hmmm...

 

[John Big Booty]

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

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

 

[vbaInet]

Give me a DLookup() that fails and I will test it here.

 

[John Big Booty]

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

 

[vbaInet]

Lol ok. What table should I focus on?

 

[John Big Booty]

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

 

[John Big Booty]

The Show Related Note button, on FRM_OrgDate is the one with the Dlookup() behind it.

 

[vbaInet]

Ok, this one will always work.

MsgBox Nz(DLookup("Note", "TBL_Dup", "DupDate = " & Format(Me.RecDate, "\#mm/dd/yyyy\#")), "Nada")