Splitting one field into several

[La di da Limey]

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

 

[SirPoonga]

What are you going to do with them seperated?

Create the extra fields first. Then make an update query and use the
format function or there are month, day, year, second, minute, hour
functions. Check access help.

 

[pietlinden]

You could create a query to get the pieces you want... what do you need
to do with the data that requires this?
check out DatePart, Month(), Year(), Hour(),Minute()... etc.
you could do all this in a query at runtime, if you wanted...

 

[fredg]

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.