Another date issue (1 Viewer)

[Isskint]

I hope someone can help on this, its driving me crazy.

I am trying to Dlookup() a value in one table based on a date. I get nothing! no errors, no nothing. If you look at the attached, i have designed a test query - qryPickTotals - and i have put in 4 calculated fields each a differenct variation of the date lookup formats - with #DATE# and without #DATE#, just the field name and queryname+field name.

All table date formats are Long Date.

What am i missing?

This is the link, attachment manager not working

https://docs.google.com/file/d/0Bwbu8tW54jgVS3lRQy1Mb3JZZEE/edit?usp=sharing

 

[pr2-eugin]

Mark try this DLookUp..

DLookUp("[ItemsPicked]","[tblPickTotals]","[PickDate]=#" & [COLOR=Red][B]Format([/B][/COLOR][qryIdentifyPickDamage]![plPickDate][COLOR=Red][B], 'mm/dd/yyyy')[/B][/COLOR] & "#")

I think this is a very well known Access International Date Problem..

 

[Isskint]

Paul,

I had tried CDate() to no effect, but never thought to try formatting the date. I have never had to invoke the format() before with dates but that certainly works.

The only times i have ever had date problems in access, is when a user free types a date - not the case here, everything is done through a date picker.

Thanks for that mate, applying that solution now.

 

[pr2-eugin]

Glad to be of some help mate.. (at least on here.. Lol)

I am not sure if this is a normal method, but I always do this, and it has never failed me..

DLookUp("[ItemsPicked]","[tblPickTotals]","[PickDate]=" &[COLOR=Red][B] CDbl[/B][/COLOR]([qryIdentifyPickDamage]![plPickDate]))

At the end of the day JET stores the values as Double precision anyway.. So this works too..