Error when comparing a date in an access database

[Jim H]

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

 

[Chris R. Timmons]

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-us/dnacc2k/html/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.

 

[John Baro]

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