Date formats in unbound text boxes

Topic

New Reply

I have an Access 97 database running under Windows 2000. I have created an unbound form to provide dates for a number of queries. The form contains 3 unbound text boxes, each with a normal date format (English dd/mm/yy). In two of the text boxes, if I enter say 12/1/2001 for 12th Jan, Access changes it to 1/12/2001 and passes 1st December to the queries. If I enter 13/1/2001, it will leave it alone. The other text box on the form works fine. Any ideas?

Reply | Quote

Replies

What do you mean by “leaves it alone”? It can’t interpret 13/1/2001 as anything but 13 January, since the last time I looked there were only 12 months in the calendar year. However, it sounds like your textbox date and your underlying data dates aren’t in the same format. Otherwise, why would Access be changing 12/1/2001 to 1/12/2001 and why would that be right from your point of view?

Reply | Quote

Thanks for the reply Charlotte. The more I think about this, the more I think it’s a Windows 2000 issue rather than Access since everything was fine under Win 98. However, just to clarify, I know it’s crazy but today all the text boxes are doing it. If I enter a day of 12 or less, the date gets screwed i.e 12/1/2001 becomes 1/12/2001. Not only does the format apparently change, but 1st December gets passed to the query when it should be 12th January. It’s as if my typing is assumed to be in American format (mm/dd/yy) which is then converted to dd/mm/yy when the text box updates.
I’ve just tried another test. If the first number I type is 13 or more, the date remains correct i.e. I type 13/12/2000, that is what is displayed and 13 Dec is (correctly) passed to the query. If the first number I type is 12 or less, the day and month are transposed i.e. 12/1/2000 becomes 1/12/2000, 1/31/2000 becomes 31/1/2000 etc. Am I making sense here? Re. one of your questions, these are unbound text boxes so there is no underlying data. My Windows Regional Settings are English (UK) with Short Date format dd/MM/yy

Reply | Quote

No, I realize they’re unbound textboxes. I was talking about the data underlying the query you said you were passing the dates to.

You may be right about Win 2000. There are a few date issues I’ve seen posted, although not this one, and it apparently is a wise move to make sure you have the latest version of the bios for your motherboard if you’re going to swim in Win2000 waters. You might try posting on the Win2000 forum, if you haven’t already done so, to see if anyone there can throw light on the problem.

Reply | Quote

Thanks for your input Charlotte. It’s nice to know somebody cares! I’m finding a few Win 2000 issues but the bios I’d forgotten about.

Reply | Quote

Erm. Just an aside. I develop on a Win 2000 based system with the English version of Win 2000 and Swiss German language settings (ie date same as English dd.mm.yy). The PC is over 2 years old (almost 3) now and it handles dates without a problem for Access 97 (english) and Access 2000 (German). So an older BIOS may not be the root of the problem especially as you said it only gives problems with 2 of the 3 date fields on the form. However I use bound fields. Do you do anything to the field contents before passing them to the Query?

Reply | Quote