Sick of dates! Date Guru needed.

[ryetee]

I'm always coming across problems with dates but no one has really been able to give me a definitive answer!!!

I'm in the UK with UK laptop running on a UK network. The laptop date format is DD/MM/YYYY so is the network. My database (Access 2010) stores the data in the same way (yeah I know it stores it as a number but it is displayedas DD/MM/YYYY).

Now I've written a wee form that takes a date from a form. The field is unbound and has a format of short date. You can ONLY enter data from the calender icon thingywotsit. The date entered is used to produce another form which is based on query. Now if I entered a date where the day is bigger than 12 everything works. If I enter a date with day 12 or less then the days and months are swapped over.

So when selecting 2 dates from August....
25/08/2014 finds records for 25/08/2014
03/08/2014 finds records for 08/03/2014

When I last had something like this I had all sorts of people saying date is stored as a number, depends on your settings etc etc I checked everything out and all was in order but still day and month were transposed. My settings are still the same and I'm guessing that the number equivalent for 3rd of August is being stored properly so is all this happening?

 

[pr2-eugin]

Please show the Query !

 

[ryetee]

Please show the Query !

Don't think that will help, According to http://www.allenbrowne.com/ser-36.html

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

So when I'm entering data say august the 5th i enter 5/8/2014. The system is then putting that through as #5/8/2014#, which in turn is interpreted as american format by access. So I'm in a catch 22 situation. Above link suggests I should be OKas I use an unbound control formatted to short date but it's not.


Hope that makes sense.
Any ideas?

 

[gemma-the-husky]

try format(somedate,"long date")

then #5/8/14# becomes the non-ambiguous 5th August 2014

otherwise #5/8/14# gets treated as 5th August while #22/5/14# gets displayed as #5/22/14# and correctly gets treated as May 22nd

 

[pr2-eugin]

If you have set the Format of the control (if it is unbound) to Short Date, then when using the format will parse the right format. Also use the MM/DD/YYYY in the Format as suggested by Allen. Or use my hack, http://pauls-learningcurve.blogspot.co.uk/2012/11/playing-around-with-dates.html

 

[ryetee]

try format(somedate,"long date")

then #5/8/14# becomes the non-ambiguous 5th August 2014

otherwise #5/8/14# gets treated as 5th August while #22/5/14# gets displayed as #5/22/14# and correctly gets treated as May 22nd

Can I use long date to access a file where data is stated as being stored as a short date? I know it's only really stored as a number but presumably I can?

I have actually got around it by swapping the MM and DD over before using it in the where statement.

 

[ryetee]

If you have set the Format of the control (if it is unbound) to Short Date, then when using the format will parse the right format. Also use the MM/DD/YYYY in the Format as suggested by Allen. Or use my hack, http://pauls-learningcurve.blogspot.co.uk/2012/11/playing-around-with-dates.html

I've adopted Allen's method and it works. I'll check out yours too!

 

[gemma-the-husky]

It's when you construct SQL that there is a problem.

eg
select * from sometable where some date = #12/10/14#

12/10/14 is taken as Dec 10th, not 12th Oct.

you have to force the date to be interpreted as a UK date. There is no problem just using dates within VBA.

 

[ryetee]

It's when you construct SQL that there is a problem.

eg
select * from sometable where some date = #12/10/14#

12/10/14 is taken as Dec 10th, not 12th Oct.

you have to force the date to be interpreted as a UK date. There is no problem just using dates within VBA.

I think I more or less understand it now!! Until I have more date processing to do and fall into the same trap!!