Convert Text string to date field

[razorking]

This has probably been asked many time before but, I'm sure others may need a refresher, as well as myself.

I have a field in a table that is a text field. It has dates like this - Nov1005. I want to actually use the filed like a date field. How can I do this in a query?

Thanks!

 

[ajetrumpet]

if you are using a 3-length string for all the months, you can use a very large nested IIF() statement to extract those different months, and then use concat. to get the rest. example:

SELECT IIF(left(field, 3) = "jan", "01", 
   IIF(left(field, 3) = "feb", "02", IIF(etc, etc...))) & 
      "/" & mid(field, 4, 2) & "/" & right(field, 2) AS [New Date Field]

 

[raskew]

If Nov1005 represents November 10, 2005 (wasn't too sure on that),
and this format is consistent throughout your table, you can use,
a combination of string functions (left(), right(), mid()) and the DateValue() function to convert it to DateTime data format, e.g.:

x = "Nov1005"
y = datevalue(left(x, 3) & "/" & mid(x,4,2) & "/" & right(x, 2))
? y
11/10/2005

...and, to show that y is truly in datetime data format

? cdbl
38666

It becomes a little hairier if your format varies, e.g.
Nov105 rather than Nov0105 to represent 1 November 2005.

x = "Nov105"
y = datevalue(left(x, 3) & "/" & mid(x, 4, iif(len(x) = 6, 1, 2)) & "/" & right(x, 2))
? y
11/1/2005
? cdbl
38657

...and, to show it works using 01 rather than 1 to designate the day:

x = "Nov0105"
y = datevalue(left(x, 3) & "/" & mid(x, 4, iif(len(x) = 6, 1, 2)) & "/" & right(x, 2))
? y
11/1/2005
? cdbl
38657

Regardless, if you set it up right, it's a 'one-liner'. No horrendous Iif() statements.


HTH - Bob

 

[razorking]

I thank you both for the replies. Am sure I am can use both pieces of information.

I went with Raskew's suggestion and that seems to work fine.

Thanks again!