using datediff on text field in query

[mssbass]

Is there a way for a query to recognize a text field as a date? My query:

DateDiff("s","ExpectedDeliverDate","ActualDeliverDate")

error: data type mismatch

 

[PHV]

What are ExpectedDeliverDate and ExpectedDeliverDate ?
My guess:
DateDiff('s',[ExpectedDeliverDate],[ActualDeliverDate])

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

 

[mssbass]

The expecteddeliverdate and actualdeliverdate are in the following format:

2005-10-06 23:59:00.000

However, these are text values - it won't subtract since they aren't a date value using the datediff function...

 

[PHV]

And what about this ?
DateDiff('s',CDate(Left(ExpectedDeliverDate,InStr(ExpectedDeliverDate,'.')-1)),CDate(Left(ActualDeliverDate,InStr(ActualDeliverDate,'.')-1)))

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

 

[mssbass]

Excellent - Thank you sooooooooooo much

 

[mssbass]

Oh yeah - how do I give the restraint as follows:

>0

"It must be greater than 0