Date format issue

T

Tasha

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
 
K

Ken Snell [MVP]

Try this:

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

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))
 
T

Tasha

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

Tasha

Douglas J. Steele said:
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)


Tasha said:
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
Click to expand...
Click to expand...
 
Top