Change Data Format

[Guest]

I currently have a field that is in the format mmddyyyy. Access reads this as
an integer and not a date. This field is in a linked table so I cannot make
changes to the field. I need to compare this field to a date formatted as
mm-dd-yy. In the past I have done something similar with a field (shipdate)
formatted yyyymmdd. With this I have changed the date in a query:

Date:= mid([shipdate],5,2) & '-' & right([shipdate],2) & '-' &
mid([shipdate],3,2)

I was trying to use a similar formula to change the mmddyyyy format, but the
mm part is only "m" until October. So, for the first 9 months of the year I
want to capture only the first digit for the month, then for the last three
months I will need to capture the first two digits. Is there a way to do
this? Thanks in advance for any help.

 

[George Nicholson]

Left(Format([YourTextDate],"00000000"),2)
Format() should left-pad YourTextDate with zeros as necessary. The entire
expression should return "01" to "12" for a month.

HTH,

 

[Guest]

I figured it out myself:

Date: IIf([P/U Date]>10000000,Left([P/U Date],2) & '-' & Mid([P/U Date],3,2)
& '-' & Right([P/U Date],2),Left([P/U Date],1) & '-' & Mid([P/U Date],3,2) &
'-' & Right([P/U Date],2))