469,326 Members | 1,597 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Error when comparing a date in an access database

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
2 4989
"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
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.

Similar topics

reply views Thread by Morten Gulbrandsen | last post: by
8 posts views Thread by Riegnman | last post: by
6 posts views Thread by rn5a | last post: by
8 posts views Thread by nishkrish | last post: by
1 post views Thread by =?ISO-8859-1?Q?Lasse_V=E5gs=E6ther_Karlsen?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.