Text conversion to date

[Dalorax]

I have a date stored as text yymmdd (e.g. 060707 would be 7/7/06). I would like to convert this format to JUL0706. I have tried Ucase(format("060707","MMM")) which yields MAR.
What am I missing here. I figure if I can convert the 07 to JUL I can use the left() and right() functions to take care of the rest.

 

[Matt Greatorex]

Try the CDate function. Off the top of my head, I forget the syntax but I know there's something in the Access Help about it.

 

[raskew]

Hi -

Added ---
cDate() won't cut it since there's no way for the system to decipher what those numbers represent. It ends up interpreting them as a double representing the way dates are stored internally by Access (number of days since 12/30/1899). With the cDate method, 60707 equates to 3/16/2066, definitely not what you're after.
---

Try this from the debug (immediate) window.

   x = "060707"
   ? ucase(format(dateserial(left(x,2), mid(x,3,2),right(x,2)),"ddmmmyy"))

   07JUL06

As an aside:

Spent a lot of years in a NATO Headquarters with from 7 to 10 different nations represented, most of whom had their distinctive date formatting. Dates were an absolute nightmare. Do you mean mm/dd/yy or dd/mm/yy or something else altogether? Way we got around that was to require all nations to display dates in the "medium date" format,e.g. 21-Jul-06. No one much liked it, and I'm not sure that any nation typically used that format when displaying dates, but we all understood it, confusion over dates disappeared, and Access understood it just fine.

For what it's worth.

HTH - Bob

 

[Dalorax]

That worked great!
Thanks!