Format the date to UK style

[Nick Pedder]

Hi,

I am running an access (shared) database and for some reason when I merge the data into word, the date dispays in the US format (i.e. 1/28/04). The strange thing is that the dates in the access tables are dispayed in the UK format (i.e. 28/1/04)

What can I do to remedy this situation? Any help would be much appreciated.

 

[Dave]

When you export the data you can split the date in the query into its
component parts

eg.

Day(DateField) & "/" & Month(DateField) & "/" & Year(DateField)

hope this helps,
Dave

Hi,

I am running an access (shared) database and for some reason when I merge

the data into word, the date dispays in the US format (i.e. 1/28/04). The
strange thing is that the dates in the access tables are dispayed in the UK
format (i.e. 28/1/04).

What can I do to remedy this situation? Any help would be much

appreciated.

 

[Nick Pedder]

Thanks for responding.

Would this require me to have seperate fields in the table for day, date & year?
I already have a considerable quantity of data so would prefer to avoid this method. It is odd, because when I first began using the database this problem was not apparent. It only happened after new year (whether this is coincidence, I am not sure)

Also when I merge information from access into word, if only one record out of a maximum of 4 is present, the date merge field associated with the records which are not present appears as 1200am. Again this has only happened since the new year.

Am I going mad, or is this some sort of post millenium bug?

 

[Allen Browne]

Nick, if this is a field in a Word document, you may be able to format it in
Word. Right-click and choose Toggle Field Codes. Then modify the code to
format the date.

If it is text, create a query in Access and format the date there. Then use
the query as the source for the merge instead of the table. In query design,
you would type something like this into the Field row of the query to
generate the date as text:
Format([YourDateFieldHere], "Short Date")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for responding.

Would this require me to have seperate fields in the table for day, date & year?
I already have a considerable quantity of data so would prefer to avoid

this method. It is odd, because when I first began using the database this
problem was not apparent. It only happened after new year (whether this is
coincidence, I am not sure)

Also when I merge information from access into word, if only one record

out of a maximum of 4 is present, the date merge field associated with the
records which are not present appears as 1200am. Again this has only
happened since the new year.

 

[Ken Snell]

No, you don't need separate fields. ACCESS stores dates as a number, and you
can change how the date is displayed (as you're seeing). The suggestion from
Dave is correct -- use functions to parse the data into your desired pieces.

ACCESS stores a time with each date as well; if you only stored a date, the
time is 12 midnight.

(A date and time value is stored this way: Date.Time
where Date is the integer portion of the double-precision number,
and Time is the decimal portion of the number.
Date is the number of days since 12/31/1899. Time is the fraction of
a 24-hour day for the time value (midnight is .0 and noon is .5 etc.)

When you export the date, ACCESS likely is using the standard Jet format,
which is the US format. You can write a query that uses the Format function
to override this and export it as UK format if you wish. Just replace the
DateField in the query with a calculated field:
UKDate: Format([DateField], "dd/mm/yyyy")


--
Ken Snell
<MS ACCESS MVP>

Thanks for responding.

Would this require me to have seperate fields in the table for day, date & year?
I already have a considerable quantity of data so would prefer to avoid

this method. It is odd, because when I first began using the database this
problem was not apparent. It only happened after new year (whether this is
coincidence, I am not sure)

Also when I merge information from access into word, if only one record

out of a maximum of 4 is present, the date merge field associated with the
records which are not present appears as 1200am. Again this has only
happened since the new year.

 

[Nick Pedder]

Thanks -that's solved my problem. Apologies if it was an easy one - i'm fairly new to access!