Expression Builder Help

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

I have the following expression. When I try this it returns and error. The
LAST_DATE_SOLD is in a YYMMDD format and I want to change it to a MM/DD/YYYY
format.

=Format(DateSerial(Left([LAST_DATE_SOLD],2),Mid([LAST_DATE_SOLD],3,2),Right(
[LAST_DATE_SOLD],2)),"mm/dd/yyyy")

Thanks
Matt
 
The best way is to convert the "LAST_DATE_SOLD" field to "YYYYMMDD".

According to Help, the "year" argument for the DateSerial function is:

Required; Integer.
Number between 100 and 9999, inclusive, or a numeric expression.


So another way you could to add 1900 or 2000 depending on the YY number.
Maybe any year between 00 and 09, add 2000 else add 1900.

Something like this:

=Format(DateSerial(Left([LAST_DATE_SOLD,2)+iif(Left([LAST_DATE_SOLD,2)>39,1900,2000), Mid([LAST_DATE_SOLD],3,2),Right( [LAST_DATE_SOLD],2)),"mm/dd/yyyy")


HTH
 
Back
Top