hi Ricardo,

Not SQL, but VBA. Take a look at CDate(), CStr(), CLng, etc in the OH.

mfG
--> stefan <--

You need to relay on VBA functions, which can be called directly from within
the JET SQL statement (and if you are within MS ACCESS), without having to
register anything (unlike using C#/Vb.Net with MS SQL Server). You can use
CDate( ), but that highly depends on the PC Setting the query runs onto, to,
silently, prefers dd-mm-yyyy over mm-dd-yyyy. It is even worse, since you
don't really control which PC will ever run your query, if the string uses
local month name, since, say, on some PC, the three letters month's name, if
not in English, the month name may not be understood at all. You can use
MID(string, start, lengthOfSubstring)  to 'slice' the original string. As
example, DateSerial( ) is a VBA function which accepts three arguments: the
year, the month and the day (all integers). So, someone can use:

   DateSerial(  INT(MID( myString, 7, 4)),  INT(MID(myString, 4, 2)),
INT(LEFT(myString, 2))  )

Note that the three INT()  are not really required, since VBA would
automatically convert the three substrings to the intended integers, but it
does not hurt explicitly forcing the conversion ourselves.
(I assumed your string was like "dd-mm-yyyy" ).

Vanderghast, Access MVP