Trouble Entering Date Criteria in Access Query

[sh55555]

I have built a query in Access using the DateSerial function to convert dates such as 20100401 to 04/01/2010. The query results work fine and the date is displayed correctly. I am now trying to enter criteria to query only certain dates. When I enter 04/01/2010 in the criteria field it displays a "data type mismatch error". I have tried entering the date criteria as #04/01/2010# and I get the same message. Ultimately I would like to create parameters to be able to enter a date range, however, I am having trouble just querying for a single date. The Date Serial expression looks like this:

Post Date: DateSerial(Left([dbo_transactions]![post_date],4),Mid([dbo_transactions]![post_date],5,2),Right([dbo_transactions]![post_date],2))


It displays the dates correctly (i.e. 04/01/2010) but it will not let me enter specific criteria.

Any suggestions?

Thanks for your help!

[ADezii]

@sh55555
There is absolutely nothing wrong with your Expression that I can see, and as a matter of fact, I also testing between a Date Range with the following SQL Statement and all works well,

SELECT DateSerial(Left(dbo_transactions!post_date,4),Mid(dbo_transactions!post_date,5,2),Right(dbo_transactions!post_date,2)) AS [Post Date]
FROM dbo_Transactions
WHERE (((DateSerial(Left(dbo_transactions!post_date,4),Mid(dbo_transactions!post_date,5,2),Right(dbo_transactions!post_date,2))) Between [Enter Beginning Date] And [Enter Ending Date]));

Is the [Post_Date] Field defined as Text?

[sh55555]

@ADezii
Yes, [Post_Date] is stored as text...does the DateSerial expression convert it to a date? Also, I understand your SQL statement, but I'm not using the SQL language in Access, I am just trying to type a date (i.e. 04/01/2010) in the "criteria" field in the design view of a query...does that make a difference?

[ADezii]

@sh55555
The DateSerial() Function returns a variant of SubType Date.

I'm not using the SQL language in Access

Not really sure what you mean.

[sh55555]

@ADezii
I am not writing SQL Language literally...I am using the boxes in the Design View of an Access query where you can simply type the criteria you want without using actual SQL language

[ADezii]

@sh55555
Understood, but it inevitably gets converted into a SQL Statement. Can you Upload the dbo_Transactions Table with some sample Data in it so that I may test it?