Importing Text

[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!

 

[Ken Snell]

Add "/" characters and then use CDate:

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

 

[Krisse]

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!

.

 

[Ken Snell]

You're welcome.

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!

.

 

[Tim Ferguson]

Add "/" characters and then use CDate:

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

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

 

[Ken Snell]

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

--
Ken Snell
<MS ACCESS MVP>

Add "/" characters and then use CDate:

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

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