moving datat

[ray]

I need some help. I have this in one field 040923131500 which is
yymmddhhmmss. This is imported for a text file. I would like to move it to
two fields.
mmddyy and hhmmss.

Can anyone help

 

[Douglas J. Steele]

Why two fields? In my opinion, it's MUCH better to combine them into a
single field. (If you really need only the date or only the time, you can
use the DateValue or TimeValue function on the combined field)

To convert what you've got into a date/time field, you'll need to split the
value into its component parts, then combine them using the DateSerial and
TimeSerial functions:

DateSerial(Left([TextDate], 2), Mid([TextDate], 3, 2), Mid([TextDate], 5, 2)
+
TimeSerial(Mid([TextDate], 7, 2), Mid([TextDate], 9, 2),
Right([TextDate], 2)

 

[John Vinson]

I need some help. I have this in one field 040923131500 which is
yymmddhhmmss. This is imported for a text file. I would like to move it to
two fields.
mmddyy and hhmmss.

Can anyone help

If you literally mean that you want to move this one text field (which
I'll call bd, for "bigdate") into two text fields, you can add the new
text fields to your table, and use an Update query updating the one to

Left([bd], 6)

and the other to

Right([bd], 6)

However, I'd suggest moving the data into an Access Date/Time field -
which can be displayed any way you like. To do so, try

CDate(Format([bd], "\2\0@@/@@/@@ @@@@")


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Douglas J. Steele]

However, I'd suggest moving the data into an Access Date/Time field -
which can be displayed any way you like. To do so, try

CDate(Format([bd], "\2\0@@/@@/@@ @@@@")

D'oh! Why didn't I think of using the Format function to convert, rather
than messing with Left, Right, Mid, DateSerial and TimeSerial!

 

[John Vinson]

However, I'd suggest moving the data into an Access Date/Time field -
which can be displayed any way you like. To do so, try

CDate(Format([bd], "\2\0@@/@@/@@ @@@@")

D'oh! Why didn't I think of using the Format function to convert, rather
than messing with Left, Right, Mid, DateSerial and TimeSerial!

Well, it doesn't work for yy/mm/dd 20'th century dates... <g>

I'd typed a couple of lines of TimeSerial(Mid... etc. when inspiration
struck. Wierd feeling.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps