Date Format in Query

[melika]

Hi,

I have a query in which I am pulling from another table. On the table I have a field called Tag, which consists of data in the form of a combo date/place. Ex) 29OCT7NY date = 10/29/2007; place = NY

When I call on the Tag field in my query, I only want the date portion. So, I used the Left function... Left([Tag],6). But I also want to format the date portion so it is always in the form 10/29/2007 in my query. I tried Format(Left([Tag],6), mm/dd/yyyy) but it did not work. I think it has something to do with the weird form of the Tag field "ddmmmy".

If anyone could help, it would be much appreciated.

Thanks!!

 

[Uncle Gizmo]

I don't believe MS Access will recognize "29OCT7" as a date. You could probably write some code to add 200 and some delimiters to it like this "29/OCT/2007" and then I suspect it would work. set up a test form and test "29/OCT/2007" if the format function correctly formats it for you You know you are on the right track.

 

[melika]

date format in query

I got it! Thanks for the advice.

I used the following code just in case people were curious:

Expr1: Format(Left([Tag],2) & "/" & Mid([Tag],3,3) & "/200" & Mid([Tag],6,1),"mm/dd/yyyy") and that returned 10/29/2007

Thanks again!