moving datat

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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
Click to expand...

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
 
John Vinson said:
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@@/@@/@@ @@:@@:@@")
Click to expand...

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 said:
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@@/@@/@@ @@:@@:@@")
Click to expand...

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

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
 
Back
Top