Date format conversion

[chris]

I am linking an access database to an accounts package (via ODBC).
Dates are available as a text string in yyyymmdd format but I want to
display them as dd/mm/yy. I am using the following code to convert it
for display in a text box on my forms.

Public Function ExchDateConvert(exdate)
y = Mid(exdate, 3, 2)
m = Mid(exdate, 5, 2)
d = Right(exdate, 2)
ExchDateConvert = d & "/" & m & "/" & y
End Function

It works but seems to be rather slow. Is there a better way to do it?
Chris

 

[Albert D. Kallal]

You could use:

ExchDateConvert = format(exdate,"yyyy/mm/dd")


The above would actually return a date type.....

I think "one" conversion vs 3 should run about 3 times faster.....

 

[Douglas J. Steele]

Sorry to argue, Albert, but that would NOT return a date type: the Format
function always returns a string. And actually, since exdate is text, you're
going to get a run-time error 13 (Type mismatch) because of using date
formatting characters with text data.

To have it return a Text value, use

ExchDateConvert = Format(exdate,"####/##/##")

(although technically I believe it should be ####-##-##)

To have it return a Date value, use

ExchDateConvert = CDate(Format(exdate,"####-##-##"))

 

[Albert D. Kallal]

Sorry to argue, Albert, but that would NOT return a date type: the Format
function always returns a string. And actually, since exdate is text,
you're going to get a run-time error 13 (Type mismatch) because of using
date formatting characters with text data.

ah, very good...

yes, I assumed we would change the function type to a date type..but,
obviously, I left that out!!!


thanks for the heads up!!!

 

[chris]

Thanks to both of you for your suggestions. However:

ExchDateConvert = format(exdate, "yyyy/mm/dd") gives me a Runtime
error 6

ExchDateConvert = format(exdate, "####/##/##") gives me the date
formatted as yyyy/mm/dd (or similarly for the version with hyphens)

ExchDateConvert = CDate(format(exdate, "####/##/##")) gives me a
runtime error 13

Is this because the original string is in the form yyyymmdd and the
reformatted version is not recognised as a date?

In my original solution I took the string apart and put it back
together again in the dd/mm/yy format.
Has anyone got any more suggestions?
Chris

 

[Douglas J. Steele]

If you notice what I posted, I suggested "####-##-##".

That worked for me when I tested it. Does it not work for you?

 

[chris]

No - ExchDateConvert = CDate(format(exdate, "####-##-##")) doesn't
work for me (runtime error 13). It seems to make no difference whether
the separators are / or -

The string I am wanting to convert is in yyyymmdd format eg 20070511
for ll May 2007

If I just use

format(exdate, "####-##-##")

It returns 2007-05-11 but this doesn't appear to be a recognised date
format when further processed through CDate hence the mismatch error.

If possible I want to get the date to display as 11/05/07 for the
above example. (or the same with "-" separators). Due to the order of
the characters in the original string perhaps the only solution is the
one I came up with originally and I will have to accept that is a
little slow.

I am using Access 2003
Chris

 

[Douglas J. Steele]

I don't know what to tell you, other than I just tested it using both Access
97 and Access 2003.

From the Immediate window:

?Format("20070511", "####-##-##")
2007-05-11
?TypeName(Format("20070511", "####-##-##"))
String
?CDate(Format("20070511", "####-##-##"))
5/11/07
?TypeName(CDate(Format("20070511", "####-##-##")))
Date

 

[John W. Vinson]

The string I am wanting to convert is in yyyymmdd format eg 20070511
for ll May 2007

I'd suggest using DateSerial(Left([textdate], 4), Mid([textdate], 5, 2),
Right([textdate], 2)

and avoid the vagaries of regional date formats altogether.

John W. Vinson [MVP]