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

Error when comparing a date in an access database

P: n/a
I'm trying to get a bunch of records based on client id and a date range. I
keep getting an error when I enclose my date string in quotes in the where
cleause.

The error is:
Microsoft JET Database Engine: Data type mismatch in criteria expression.

If I remove the quotes and use MyDateVar.ToShortDateString the select
executes but ignores the date in the resulting dataset. I get all dates.

Here is my select as it goes into the OleDbDataAdapter:
select * from TableA where ClientId = 1 and RecDate between '7/15/2003
12:00:00 AM' and '7/15/2003 11:59:59 PM'

This select gives me the above error. I get the error even if I use
ToShortDateString if the date is quoted. I never had a problem with this
using VS.NET2002 and SqlClients.

This is VS.NET2003 and an Access database.

Any help or insight would be greatly appreciated.

Thanks,
Jim
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Jim H" <ji*@nospam.nowhere.com> wrote in
news:Oi**************@tk2msftngp13.phx.gbl:
I'm trying to get a bunch of records based on client id and a
date range. I keep getting an error when I enclose my date
string in quotes in the where cleause.

The error is:
Microsoft JET Database Engine: Data type mismatch in criteria
expression.

If I remove the quotes and use MyDateVar.ToShortDateString the
select executes but ignores the date in the resulting dataset.
I get all dates.

Here is my select as it goes into the OleDbDataAdapter:
select * from TableA where ClientId = 1 and RecDate between
'7/15/2003 12:00:00 AM' and '7/15/2003 11:59:59 PM'

This select gives me the above error. I get the error even if I
use ToShortDateString if the date is quoted. I never had a
problem with this using VS.NET2002 and SqlClients.

This is VS.NET2003 and an Access database.


Jim,

http://msdn.microsoft.com/library/en.../acfundsql.asp

In MS Access SQL, wrap date literals with #:

select * from TableA where ClientId = 1 and RecDate between
#7/15/2003 12:00:00 AM# and #7/15/2003 11:59:59 PM#

If you use OleDbParameters, you don't have to provide the # wrapping:

string sql =
"select * from TableA where ClientId = 1 and RecDate " +
"between @firstDate and @secondDate";

OleDbParameter firstDateParam =
new OleDbParameter("@firstDate", OleDbType.DBDate);
firstDateParam.Value = new DateTime(2003, 15, 7, 12, 0, 0);
firstDateParam.Direction = ParameterDirection.Input;

// Similar code for @secondDate...

OleDbCommand command = new OleDbCommand(sql, connection);
command.Parameters.Add(firstDateParam);
command.Parameters.Add(secondDateParam);
Hope this helps.

Chris.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/
Nov 13 '05 #2

P: n/a
Another thing you might want to keep in mind is the Universal Date / Time
Format
Being
yyyy-mm-dd hh:mm:ss
(24 hour clock)
This has solved datetime problems in the past for me. Wrapping it in single
quotes should not only be acceptable but necessary for access.
HTH
JB
"Jim H" <ji*@nospam.nowhere.com> wrote in message
news:Oi**************@tk2msftngp13.phx.gbl...
I'm trying to get a bunch of records based on client id and a date range. I keep getting an error when I enclose my date string in quotes in the where
cleause.

The error is:
Microsoft JET Database Engine: Data type mismatch in criteria expression.

If I remove the quotes and use MyDateVar.ToShortDateString the select
executes but ignores the date in the resulting dataset. I get all dates.

Here is my select as it goes into the OleDbDataAdapter:
select * from TableA where ClientId = 1 and RecDate between '7/15/2003
12:00:00 AM' and '7/15/2003 11:59:59 PM'

This select gives me the above error. I get the error even if I use
ToShortDateString if the date is quoted. I never had a problem with this
using VS.NET2002 and SqlClients.

This is VS.NET2003 and an Access database.

Any help or insight would be greatly appreciated.

Thanks,
Jim

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.