Importing Text

  • Thread starter Thread starter Krisse
  • Start date Start date
K

Krisse

How do I load a text field containing a date in mmddyyyy
format (e.g. 11032003) into a Date/Time field?

CDate function doesn't seem to work.

Thanks!
 
Add "/" characters and then use CDate:

CDate(Left([TextField], 2" & "/" & Mid([TextField], 3, 2) & "/" &
Right([TextField], 2))
 
Thanks, Ken! It worked like a charm!

-----Original Message-----
Add "/" characters and then use CDate:

CDate(Left([TextField], 2" & "/" & Mid([TextField], 3, 2) & "/" &
Right([TextField], 2))

--
Ken Snell
<MS ACCESS MVP>

How do I load a text field containing a date in mmddyyyy
format (e.g. 11032003) into a Date/Time field?

CDate function doesn't seem to work.

Thanks!
Click to expand...


.
Click to expand...
 
You're welcome.

Krisse said:
Thanks, Ken! It worked like a charm!

-----Original Message-----
Add "/" characters and then use CDate:

CDate(Left([TextField], 2" & "/" & Mid([TextField], 3, 2) & "/" &
Right([TextField], 2))

--
Ken Snell
<MS ACCESS MVP>

How do I load a text field containing a date in mmddyyyy
format (e.g. 11032003) into a Date/Time field?

CDate function doesn't seem to work.

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Add "/" characters and then use CDate:

CDate(Left([TextField], 2" & "/" & Mid([TextField], 3, 2) & "/" &
Right([TextField], 2))
Click to expand...

CDate uses local Regional Settings, though, and this will fail outside of N
America. It might be safer to use the DateSerial function:

DateSerial(CInt(Mid(TextField,5,4)), _
CInt(Mid(TextField,1,2)), _
CInt(Mid(TextField,3,2)))

just in case the new temp sets the Control Panel back to normal (ahem!).

B Wishes


Tim F
 
Good point, Tim......guess you can tell that I do all my work in the
northern clime!

--
Ken Snell
<MS ACCESS MVP>

Tim Ferguson said:
Add "/" characters and then use CDate:

CDate(Left([TextField], 2" & "/" & Mid([TextField], 3, 2) & "/" &
Right([TextField], 2))
Click to expand...

CDate uses local Regional Settings, though, and this will fail outside of N
America. It might be safer to use the DateSerial function:

DateSerial(CInt(Mid(TextField,5,4)), _
CInt(Mid(TextField,1,2)), _
CInt(Mid(TextField,3,2)))

just in case the new temp sets the Control Panel back to normal (ahem!).

B Wishes


Tim F
Click to expand...
 
Back
Top