I am trying to query by date and I keep getting this error (data type mismatch in criteria expression). I have read a lot of posts about date format and I don't see any problems with that. I think it might not like something about the way the date was created in another query possibly.
The original date format was not recognizable by access: "Wed Jan 20 21:01:31 2010". I used a long and drawn out expression that converts Jan to 01, and extracted the day, year, etc into their own fields. Then I used DateSerial to put the numbers back together into "01/20/2010".
In another query, I reference this reconstructed date and it displays correctly. But when I try to show records after a certain date using Criteria in design mode, for example >#08/08/2010# , I get the error.
After changing the format several times in both this query and the other referenced queries, I tried something else. I converted the dates to numbers, expr1: CDbl([Date]) . Now I have just numbers like 40427. They appear right justified like a number should. I try to show records after a certain number, for example >40000 , I get the same error...
When I go back to the original query that constructs this date, I cannot use any criteria on the date here either. Here I get a different error. It says Enter Parameter Value for my month field which is the super long IIf expression. This probably doesnt matter, but if I enter a value, the querry does display for a momment, then I get the original error message and the query turns into all #name?
Then when I try to add Criteria to the field with the month expression, I get an expression too complex error.
Any suggestions at what I should do differently?
Here is the month expression:
fxMonth: IIf(Left((Right([first fix LMT],20)),3)="Jan",1,IIf(Left((Right([first fix LMT],20)),3)="Feb",2,IIf(Left((Right([first fix LMT],20)),3)="Mar",3,IIf(Left((Right([first fix LMT],20)),3)="Apr",4,IIf(Left((Right([first fix LMT],20)),3)="May",5,IIf(Left((Right([first fix LMT],20)),3)="Jun",6,IIf(Left((Right([first fix LMT],20)),3)="Jul",7,IIf(Left((Right([first fix LMT],20)),3)="Aug",8,IIf(Left((Right([first fix .............