Error with dates

[Richard]

Hi

I am encountering some problems with dates. The regional settings has been
set to dd/mm/yy. The problem is that when I enter 11/02/04 in the form's
date field, it saves as 2 Nov. Even when I set the default value as Date(),
it turns out different.

How do I correct this? Many thanks in advance.

Richard

 

[Naresh Nichani MVP]

Hi:

Open the form in Design View. Select TextBox bound to Date field and change
the Format Property as needed.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[Richard]

Hi Naresh

Thanks for the tip. It actually works well with bound forms. But when I use
an unbound form and use an action query to insert data into the table, the
format will come up as mm/dd/yy.
Why is it so?

Thanks again
Richard

 

[Douglas J. Steele]

Unfortunately, that's how Access was designed: to use the US format of
mm/dd/yyyy by default.

Fortunately, since you're using an Action Query to insert the data, rather
than having a bound field, it's easy to reformat your dates to get Access to
handle them correctly.

Take a look at Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html for some suggestions.

It's important to realize that the date values themselves don't have a
format (they're stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec, 1899, and
the decimal part represents the time as a fraction of a day). That means
that as long as you can get Access to recognize your dates correctly, you
shouldn't have any further problems.

 

[Richard]

Hi Douglas

Thanks for the advice.

Richard

Unfortunately, that's how Access was designed: to use the US format of
mm/dd/yyyy by default.

Fortunately, since you're using an Action Query to insert the data, rather
than having a bound field, it's easy to reformat your dates to get Access to
handle them correctly.

Take a look at Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html for some suggestions.

It's important to realize that the date values themselves don't have a
format (they're stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec, 1899, and
the decimal part represents the time as a fraction of a day). That means
that as long as you can get Access to recognize your dates correctly, you
shouldn't have any further problems.