You are aware that you have a string and that it is highly dependant on the
regional setting as to get the result right, right?
In fact, it is more complex than that, but just for illustration of the
possible problems (that you seem to not be fully aware), try the simple line
of code in the Immediate debug window:
? Format( CDate("31/12/2007"), "long date"), Format(CDate("12/31/2007"),
"long date")
Monday, December 31, 2007
Monday, December 31, 2007
See, you get the same date, but the first one was typed as dd/mm/yyyy and
the second as "mm/dd/yyyy".
The conclusion is to AVOID strings representation of date, if you don't
control very well the regional setting. Definitively "10/11/12" can be in
October (in USA), or in November (in England) ; the 10th of November 2012,
or, with ISO, the 12th of November 2010. String, as a date, is far, far,
from being 'safe'.
But sure, it is your data, not mine, after all.
Vanderghast, Access MVP
pon said:
I created a expression
TRANS_DATE: Format([FGBTRNH_TRANS_DATE],"mm/dd/yyyy hh:nn:ss")
which worked.
Thank you
pon
Michel Walsh said:
You don't have if the date are date_time. By default, those without time
will be at midnight (00:00:00)
If the values are strings, better to have your default regional setting
to
dd/mm/yyyy (In the USA, it would probably be mm/dd/yyyy ). If you
cannot
change the regional setting (because it has to run on a PC which is not
yours), you can use DateSerial:
DateSerial( Mid(yourString, 7, 4), Mid(yourString, 4, 2),
Mid(yourString,
1, 2) ) + TimeValue( CDate( yourString) )
to convert the data into a date_time value.
Vanderghast, Access MVP