By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Trouble Entering Date Criteria in Access Query

P: 3
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!
Apr 30 '10 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
@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,
Expand|Select|Wrap|Line Numbers
  1. SELECT DateSerial(Left(dbo_transactions!post_date,4),Mid(dbo_transactions!post_date,5,2),Right(dbo_transactions!post_date,2)) AS [Post Date]
  2. FROM dbo_Transactions
  3. 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?
Apr 30 '10 #2

P: 3
@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?
Apr 30 '10 #3

ADezii
Expert 5K+
P: 8,597
@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.
Apr 30 '10 #4

P: 3
@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
Apr 30 '10 #5

ADezii
Expert 5K+
P: 8,597
@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?
Apr 30 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.