Date format issue

[Guest]

I have three linked dbf tables in which dates are stored as numbers not
date/time in the format of yyyymmdd. I need the user to see the date as
mm/dd/yyyy. I cannot change the field data type on the linked tables for
multiple reasons so this is not an option. Can anyone tell me an easy way to
format this number? I tried using the formula
Left(Right([field],4),2)&"/"&Right([field],2)&"/" &left([Field],4) but I get
the error message that the expression is too complicated to be evaluated. I
either want to do the formatting in the query or on the form. There are about
10 dates within a single query that need to be formatted this way. Any
suggestions?

TIA
Tasha

 

[Allen Browne]

Try:
DateSerial(Left([f],4), Mid([f],5,2), Right([f],2))

 

[Ken Snell [MVP]]

Try this:

Format(DateSerial(Left([Field], 4), Mid([Field], 5, 2), Right([Field], 2)),
"mm/dd/yyyy")

 

[Douglas J. Steele]

See whether DateSerial(Left([Field], 4), Mid([Field], 5, 2), Right([Field],
2)) works.

If not, try using CStr or Format to convert Field to a string before using
the String functions on it:

DateSerial(Left(CStr([Field]), 4), Mid(CStr([Field]), 5, 2),
Right(CStr([Field]), 2))

 

[Guest]

Thanks to All. I don't know what I would do without your help. Worked
wonderfully!!

Tasha

See whether DateSerial(Left([Field], 4), Mid([Field], 5, 2), Right([Field],
2)) works.

If not, try using CStr or Format to convert Field to a string before using
the String functions on it:

DateSerial(Left(CStr([Field]), 4), Mid(CStr([Field]), 5, 2),
Right(CStr([Field]), 2))


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have three linked dbf tables in which dates are stored as numbers not
date/time in the format of yyyymmdd. I need the user to see the date as
mm/dd/yyyy. I cannot change the field data type on the linked tables for
multiple reasons so this is not an option. Can anyone tell me an easy way to
format this number? I tried using the formula
Left(Right([field],4),2)&"/"&Right([field],2)&"/" &left([Field],4) but I get
the error message that the expression is too complicated to be evaluated. I
either want to do the formatting in the query or on the form. There are about
10 dates within a single query that need to be formatted this way. Any
suggestions?

TIA
Tasha