text to date comparison

[gebuh]

Hi all- I'm creating an update query.
I have a field [dateLastEdit] that is stored as text, I have to compare it to a date entered on a form by user [date_from] (I need to select all dates greater than this) but I'm running into problems:
when I use date_from in text or date format and compare it to dateLastEdit in text format the comparison is made in numeric order(so 12/02/2006 is selected as well as 12/02/2005 when date_from is 12/01/2006)

SELECT PTSData.LastEditDate
FROM PTSData
WHERE (((PTSData.LastEditDate)>=[Forms]![chMgmt_frm]![date_from]));

when I use date_from in date format and compare it to dateLastEdit converted to date format I get "expression typed incorrectly or it is too complex to be evaluated" msg.

SELECT DateValue([LastEditDate]) AS Expr1
FROM PTSData
WHERE (((DateValue([LastEditDate]))>=[Forms]![chMgmt_frm]![date_from]));

[LastEditDate]'s format is always mm/dd/yyyy hh:mm:ss, but as text, not date data type.
I've tried CDate with same results, isolating it in its own query to test it, using hardwired dates as criteria, but no joy, am I missing something obvious?

forgot to add- I've used all the above with left also to select only date portion with same results

 

[Bat17]

does just the conversion work OK

SELECT DateValue([LastEditDate]) AS Expr1
FROM PTSData

Just wondering if you have some invalid data throwing a spanner in the works

Peter

 

[Dennisk]

have you tried converting the text date using datevalue function. This will convert a string to a Date datatype.

 

[gebuh]

no it doesn't - I get the same results, I've looked thru all the records- they're all in date/time format

does just the conversion work OK

SELECT DateValue([LastEditDate]) AS Expr1
FROM PTSData

Just wondering if you have some invalid data throwing a spanner in the works

Peter

 

[Bat17]

what actualy happens when you run
SELECT DateValue([LastEditDate]) AS Expr1
FROM PTSData
Does the query fail to run giving a warning or are you getting #Error in the Expr1 field rather than dates?

Peter

 

[gebuh]

I get a warning-
"expression typed incorrectly or it is too complex to be evaluated"
I found a work around- it ain't pretty but it works, I just don't understand why using dateValue wouldn't work, I ended up doing this:
get year, month and day from lastEditDate

Mid([lastEditDate],7,4) & Left([lastEditDate],2) & Mid([lastEditDate],4,2)

change date_from and date_to to same format, and check if lastEditDate is between.

Between Format([Forms]![chMgmt_frm]![date_from],"yyyymmdd") And Format([Forms]![chMgmt_frm]![date_to],"yyyymmdd")

I'd still appreciate if anyone knows why it won't work using functions though.

what actualy happens when you run
SELECT DateValue([LastEditDate]) AS Expr1
FROM PTSData
Does the query fail to run giving a warning or are you getting #Error in the Expr1 field rather than dates?

Peter