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
"Ricardo Vazquez" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi everybody!
>
> Is there a SQL command for MS Access similar to the CONVERT command in
> Transact-SQL?
> And could you please help me an usage example?
>
> I need to change a DateTime format, something like this:
>
> (this is what I would do in SQLServer):
>
> SELECT distinct(CONVERT(char(11),dateField,105))
> from Table1
> order by CONVERT(char(11),dateField,105)
>
> Thank you!
>
> Ricardo.
>
|
