Date Conversion Issue

[abbaddon223]

Hello experts - I'm hoping someone can help me please?

I'm having an issue (I think) with the VBA Date() function putting an american date in a field (even though is displays in British format)

The whole issue is;

I have a field calls Date_Today which is being updated with:

[Date_Today].value = Date()

Is see 07/06/2013

This is for the purpose of a dlookup putting a "Yes" or "No" in another field

[Weekly_Report_Send]

The lookup is

[Weekly_Report_Send].Value = DLookup("Send", "Flash Report Weekly Dates", "Weekly_Date=#" & Date_Today & "#")

The aim is that in my table "Flash Report Weekly Dates" - when todays date matches a send date, it puts a "Yes" into the [Weekly_Report_Send] control and the rest of my command actions some stuff

The issue is that the value in the [Date_Today] thinks is lookup up the 06/07/2013 date not 07/06/2013 :banghead:

Any help appreciated!!!!

 

[pr2-eugin]

This is a very well know Access Issue : International Dates in Access.

 

[abbaddon223]

Hi Paul,

Thanks for responding - I did find this page too - but I'm still at a loss as to what to do about it?

Best,

Phil.

 

[pr2-eugin]

What happens when you just use Date() instead of putting it in a control and then using that value?

DLookup("Send", "Flash Report Weekly Dates", "Weekly_Date=#" & Date() & "#")

 

[abbaddon223]

Hi Paul - same issue I'm afraid. Thank you for trying to help

 

[pr2-eugin]

Is the control Unbound? If so see what is the Format set to. It should say Short date..

If that is all correct, then I sometime am a bit cheeky and do this..

DLookup("Send", "Flash Report Weekly Dates", "Weekly_Date=" & CDbl(Date))

 

[abbaddon223]

Yes to unbound and it looked like cheeky worked!!

Could I ask what that did and why it worked for next time please?

Thank you so much for helping!

 

[pr2-eugin]

Yes to unbound and it looked like cheeky worked!!

That's good..

Could I ask what that did and why it worked for next time please?

Sure you can, but it will cost you a SMILE.. There it is..

In Access Dates are stored as Double values.. The dates you see are formatted to be shown as proper date.. So when you store values at the end of the day it is going to be double values.. So why not cut the middle man and query that value, I say.. Hope that makes sense.. I have a small discussion HERE..

Thank you so much for helping!

My pleasure..

 

[Brianwarnock]

Ok maybe old age and senility are finally catching up with me, but isn't Date() a Double and only becomes a date string if you force it to.

Brian