Formatting text Field to Date

[truitt20]

Currently a linked table has a "Date" field that is data type text. Field is populated as such:

20070418

I have written an expression to get it 04/18/2007, but when i try to link to other tables via queries I get the wonderful data type mismatch b/c the other tables are all using data type Date/Time. Any way to get around this?

thanks

 

[SkipVought]

Hi,

"I have written an expression to get it 04/18/2007..."

Just because the data LOOKS like 04/18/2007, does not necessarlily mean that it's a REAT DATE.

What is your expression?

Does your expression CONVERT the TEXT to a REAL DATE? That is what you need to do if you are comparing this with other DATES.

Skip,

_[red][/red]

 

[truitt20]

My expression looks like:

Period End Date: Mid(SPC_StdPos_D![Period End Date],5,2) & "/" & Right(SPC_StdPos_D![Period End Date],2) & "/" & Left(SPC_StdPos_D![Period End Date],4)

I also tried to format the field properties to short dat as well, but to no avail.

thanks

 

[LarrySteele]

You need to coerce your output into a date using the CDate function:
[tt]
Period End Date: CDate(Mid(SPC_StdPos_D![Period End Date],5,2) & "/" & Right(SPC_StdPos_D![Period End Date],2) & "/" & Left(SPC_StdPos_D![Period End Date],4))[/tt]

HTH,
Larry

 

[truitt20]

Perfect! Thank guys. Never new of the CDate function