MS Access Query

[bimmernut]

Can someone figure out for me why this simple query returns an empty recordset? I tried it all different ways but it will not return any records. The only way it works if if I use 'OR' instead of 'AND'. I need it to work with AND. Here is the query:

SELECT EXPENSE.ID, EXPENSE.[Expense Description] AS Expense, EXPENSE.[Expense Date] AS [Date], EXPENSE.[Expense Amount] AS Amount, EXPENSE.[Expense PayMethod] AS [Payment Method], EXPENSE.[Expense Category] AS Category, EXPENSE.[Expense Notes] AS Notes From EXPENSE WHERE ((Left$([EXPENSE].[Expense Date],2))='01' AND (Right$([EXPENSE].[Expense Date],4))='2003') ORDER by EXPENSE.[Expense Description] ASC,EXPENSE.[Expense Date] ASC


Any help is greatly appreciated.
Thanks

 

[bimmernut]

Thanks for the reply.

Yes, there is data that fits that criteria. Also, the only reason I have it as a string is because if I try it the proper way (as you suggested) MS Access comes back with "Data Type Mismatch in Criteria". The table field is definetaly a DATE field but I just can't get it to work. Any other thoughts would be helpful.

 

[gdanalakshmi]

If you are getting the parameter from a form's control like say a textbox, then it will be a string. So, type cast and convert to data type matching the field.