ACCESS Date Conversion 1

[md092686]

I have a field column with dates in this format 20070506. I need to convert this date to: 5/6/2007 with slashes but have the date appear as mm/dd/yyyy. Is there a quick conversion function to do this?

Thanks


md092686

 

[SkipVought]

Hi,

Expr: DateSerial(mid([YourFld],1,4)
,mid([YourFld],5,2),mid([YourFld],7,2))

converts your STRING to a DATE (which is just a NUMBER).

FORMAT to suite, (like mm/dd/yy)


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[md092686]

The date is formatted as a Number in Access from the Mainframe. I have placed your Expression in the criteria part as: DateSerial(mid([ABCDate],1,4)
,mid([ABCDate],5,2),mid([ABCDate],7,2))
Yet, when this is run, i get no results. Don't you need the "/" between & somehow?

 

[SkipVought]

"I have placed your Expression in the criteria part as..."

This is NOT a criteria.

It is in the SELECT statement, in place of your field name.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

also...

"The date is formatted as a Number in Access from the Mainframe"

It should not be a number. Will you ever do arithmetic with these values?

The answer is NO. It is a STRING of NUMERIC CHARACTERS.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[md092686]

Skip,

I knew that I guess I am not thinking well this Monday afternoon. Yes, you can never do arithmetic with a string.
Anyhow, I got it to work.

Thanks

md092686

 

[SkipVought]

"The date is formatted as a Number in Access from the Mainframe"

THAT is what I was referring to that ought to be a string.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[md092686]

Skip,

I appreciate you helping me. I got it to work fine!

Thanks

md092686