formatting imported fields-help

[janet]

Hello,
I am trying to convert an imported field from my database but my code is not
working . . .please help. The field date imports as follow 100104 (for 2010)
and 91231 (for 2009). . . being 9 the year, 12 the month, and 31 the day for
2009 and respectively for 2010. . . and i need to convert it as follow. . .
mm/dd/yyyy. . . how can i process this ? ?Please advise.Thanks.

 

[Allen Browne]

Hello,
I am trying to convert an imported field from my database but my code is
not working . . .please help. The field date imports as follow 100104 (for
2010) and 91231 (for 2009). . . being 9 the year, 12 the month, and 31 the
day for 2009 and respectively for 2010. . . and i need to convert it as
follow. . . mm/dd/yyyy. . . how can i process this ? ?Please
advise.Thanks.

If your imported field is named [d], either expression below should work.

If it's a Number field, use:
DateSerial([d] \ 10000, ([d] \ 100) Mod 100, [d] Mod 100)

If it's a Text field, use:
DateSerial(Left(Format([d], "000000"),2), Mid(Format([d], "000000"),2),
Right([d],2))

Either way, you probably need to test for Null to avoid errors:
IIf([d] Is Null, Null, DateSerial([d] \ 10000, ([d] \ 100) Mod 100, [d] Mod
1000))

 

[Tom van Stiphout]

On Tue, 19 Jan 2010 11:44:09 +0800, "Allen Browne"

You probably meant:
Mid(Format([d], "000000"),3,2)

-Tom.
Microsoft Access MVP

Hello,
I am trying to convert an imported field from my database but my code is
not working . . .please help. The field date imports as follow 100104 (for
2010) and 91231 (for 2009). . . being 9 the year, 12 the month, and 31 the
day for 2009 and respectively for 2010. . . and i need to convert it as
follow. . . mm/dd/yyyy. . . how can i process this ? ?Please
advise.Thanks.

If your imported field is named [d], either expression below should work.

If it's a Number field, use:
DateSerial([d] \ 10000, ([d] \ 100) Mod 100, [d] Mod 100)

If it's a Text field, use:
DateSerial(Left(Format([d], "000000"),2), Mid(Format([d], "000000"),2),
Right([d],2))

Either way, you probably need to test for Null to avoid errors:
IIf([d] Is Null, Null, DateSerial([d] \ 10000, ([d] \ 100) Mod 100, [d] Mod
1000))

 

[Allen Browne]

You probably meant:
Mid(Format([d], "000000"),3,2)

Thanks, Tom