On Wed, 09 Mar 2005 16:47:29 GMT, La di da Limey wrote:
Hi,
I have a field in a database called "Timestamp" which has the date and time
of an event, for example:
"01/02/2002 09:07:59"
The format is MM/DD/YYYY HH:MM:SS
I want to chop the field into six seperate fields of MM, DD, YYYY, HH, MM &
SS but cannot find a way of doing this in Access without resorting to
exporting as a text file and re-importing as a fixed width file.
Can anyone help?
Many thanks
L
Why bother?
Access stores date/time as a double number, counting the number of
days from 12/30/1899, and time as the percentage of a 24 hour day from
midnight.
So 3/9/2005 09:02:00 AM is stored as 38420.3766898148.
How the date is formatted is not relevant as long as it is a date
datatype field.
If the date field already contains the full date and time, all you
need do, whenever you wish to show those separate values, is use:
=Year([DateField]) (year)
=DatePart("m",[DateField]) (month)
=Month([DateField]) (month)
=DatePart("d",[DateField]) (Date day)
= DatePart("h',[dateField]) (hour)
=DatePart("n",[dateField]) (minute)
etc.
Look up DatePartI(), Month(), Year(), WeekDay(), etc. for additional
arguments to return the parts of a date datatype field.
Or you can simply format the date field to show the part you want:
=Format(DateField],"mmm") will display March.
Hope this helps.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.