DateValue doesn't work for Date Text datatype 2

[TTF]

Having a field in Access97 that populated with
12/20/2004 format date and 12202004 format Date. Datatype of the column however is Text. I could Append it to a right datatype table but can't use recommended solution
Exp1ateValue([Date_Column])

Gives me an "Data type mismatch error"

I will have to select date in ListBox to print Report for this date. Will Text datatype affect my Report running? Thanks

 

[TTF]

So when writing Select Payday from Table where Exp1ateValue([Payday])
gives "datatype mismatch error" and DateValue should take care of it, right?

 

[PHV]

IIf(InStr([Date_Column],'/')>0,DateValue([Date_Column]),DateSerial(Right([Date_Column],4),Mid([Date_Column],4,2),Left([Date_Column],2)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SJMcAbney]

When using the DateValue() function the string argument you pass to it must be recognisable as a date. 20040120, for example, is not seen as a date and, as you have been rightly shown, has to be broken down into its separate chunks - using String functions - and created as a date with the DateSerial function.

Stewart J. McAbney | Talk History

 

[TTF]

Thanks to both of you, you made my day!

 

[TTF]

Little correction to a formula just in case someone will stumble upon it in future:
IIf(InStr([Pay Date],'/')>0,DateValue([Pay Date]),DateSerial(Right([Pay Date],4),Left([Pay Date],2),Mid([Pay Date],3,2)))