Data Type Mismatch in Criteria Expression

[cavscout]

I have a set of queries that will Approve and Revoke Credit Approval based on given criteria. The field giving me a problem is the [ReviewDate]. I want the [ReviewDate] to be not more than 6 months old. This date is held in a table I'm linking to via ODBC and the field is formatted as text (I cannot change this). To get around the text, I am using

((Date()-DateValue([ReviewDate]))/30) < 6 = Approved

The Approval Query works as it should.

When I go to revoke the Approval I simply reversed the logic in the criteria:

((Date()-DateValue([USER_FIELD_1]))/30) >= 6 = Revoke

I get the Data Type Mismatch Error. If I remove the Criteria from the query it works.

I tried using ' Like "6" ' as my criteria but no luck there. I Still get Data Type Mismatch.

Any ideas? Thanks in advance.

Access 97 on Win2K

 

[Mile-O]

DateValue returns a string and you can't, mathematically, subtract a string from a date. Surround the DateValue with CDate()

 

[cavscout]

Thanks for your response Mile. I tried the CDate() and it gave me a Data Mismatch Error also. But I figured to try and remove the DateValue() and it ran fine. Then to make things more odd, I tried removing DateValue() from the Approve query and then that one failed. I may not have thought thru this far enough but it seems that the same field is behaving differenetly in different queries.

Thanks for setting me straight on the DateValue(), not sure how I misread that function. Ignorance is bliss

 

[Mile-O]

Also, why have you got to assignment operators in the same expression?

< and =