Problem with a DateSerial Function

[razkowski]

Hello-

I'm having trouble with a DateSerial function. Actually, the function itself is working fine. The problem is when I use a second query with the "between and" function that i run into problems.

So, I'm using query 1 with the DateSerial function to adjust dates saved as text (example "20110401") to short date format ("4/1/2011"). That is working fine. However, in query 2, i use that adjusted date field with a "between and" criteria ("Between #1/1/2011# and Date()). This appears to work at first as the results load and look normal. However, if I try to work with the results at all (click the arrow for the last field, try to auto fit all fields etc), the "Data type mismatch in criteria expression" error appears and all fields change to "#Name?"

I'm sure this is due to an issue with the DateSerial adjusted dates but I don't know what to do to fix.

I appreciate any help that can be provided.

[ssanfu]

Maybe you could wrap the date serial function in the CDate() function

CDate(DateSerial(yyyy,mm,dd))

Would have to see the SQL for Query1 and query2.