Data type mismatch in criteria expression

[Peter Hibbs]

With Access 2000 I have the following simple query.

SELECT tblDiary.StartTime, tblDiary.EndTime, DateValue([StartTime]) AS
StartDate, DateValue([EndTime]) AS EndDate, tblDiary.Notes,
tblCategories.ColorCode, tblCategories.Priority
FROM tblCategories INNER JOIN tblDiary ON tblCategories.Category =
tblDiary.Category
ORDER BY tblDiary.StartTime;

The table (tblDiary) has a list of appointments where the StartTime
and EndTime fields ALL hold valid date + time values. The StartDate
and EndDate are alias fields which return just the date part of the
start and end times.

In VBA code I have :-

Dim vDate As Date

vDate = Date
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments
WHERE StartDate <= #" & Format(vDate, "yyyy/m/d") & "# AND EndDate >=
#" & Format(vDate, "yyyy/m/d") & "#")

(All on one line).

The idea is to return all records where the date in vDate falls within
the range of the start date and end date for each record.

For example :-

StartTime EndTime
19/05/2009 09:00:00 19/05/2009 11:00:00
20/05/2009 09:00:00 22/05/2009 11:00:00
23/05/2009 09:00:00 24/05/2009 11:00:00

If vDate is set to 21/05/2009 (the actual time part is irrelevant for
this part of the code) then the recordset should return the second
record only because that record is the only one where the start date
is less than or equal to vDate AND the end date is greater than or
equal to vDate. Of course, in practice, the actual date in vDate is
determined by the user to return different records.

When this runs I get the "Data type mismatch in criteria expression."
error on this line of the code. If I change EndDate to EndTime I do
not get the error and the code works OK except, of course, the time
element is included which (probably) means that I will not get the
correct results. I also get the same error if I use DateValue(EndTime)
instead of EndDate although using DateValue(StartTime) does NOT
produce an error.

I don't understand why the EndDate (and DateValue(EndTime)) will
produce an error where StartDate does not. Anyone have any ideas on
why and how to fix it.

Peter Hibbs.

 

[Roger Carlson]

I don't understand why you have the Format function in there. Format
converts a date to a formatted string, so your delimiter (# signs) are
wrong. I think that's giving you your type mismatch. If you want to return
just the date from a date/time, use the DateValue function instead.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Peter Hibbs]

Roger,

I am in the UK so I thought I needed to transform the date format to
International (or US) format, perhaps not in this case.

Anyway, I removed that and tried this :-

WHERE StartDate <= #" & vDate & "# AND EndDate >= #" & vDate & "#")

for the WHERE clause but I get exactly the same error. What do you
think the syntax should be. I would not think I would need the
DateValue function in the VBA code because the StartDate and EndDate
fields are created in the query as date only fields.

Peter Hibbs.

 

[John Spencer MVP]

My guess is that you have one or more records where ENDTIME contains a null
value. This is almost always the case when I see this type of error.

Try modifying your source query to the following and see if it works:

SELECT tblDiary.StartTime, tblDiary.EndTime
, IIF(IsDate([StartTime]),DateValue([StartTime],Null) AS StartDate
, IIF(IsDate([EndTime]),DateValue([EndTime],Null) AS EndDate
, tblDiary.Notes
, tblCategories.ColorCode, tblCategories.Priority
FROM tblCategories INNER JOIN tblDiary
ON tblCategories.Category = tblDiary.Category
ORDER BY tblDiary.StartTime;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

With Access 2000 I have the following simple query.

SELECT tblDiary.StartTime, tblDiary.EndTime, DateValue([StartTime]) AS
StartDate, DateValue([EndTime]) AS EndDate, tblDiary.Notes,
tblCategories.ColorCode, tblCategories.Priority
FROM tblCategories INNER JOIN tblDiary ON tblCategories.Category =
tblDiary.Category
ORDER BY tblDiary.StartTime;

The table (tblDiary) has a list of appointments where the StartTime
and EndTime fields ALL hold valid date + time values. The StartDate
and EndDate are alias fields which return just the date part of the
start and end times.

In VBA code I have :-

Dim vDate As Date

vDate = Date
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments
WHERE StartDate <= #" & Format(vDate, "yyyy/m/d") & "# AND EndDate >=
#" & Format(vDate, "yyyy/m/d") & "#")

(All on one line).

The idea is to return all records where the date in vDate falls within
the range of the start date and end date for each record.

For example :-

StartTime EndTime
19/05/2009 09:00:00 19/05/2009 11:00:00
20/05/2009 09:00:00 22/05/2009 11:00:00
23/05/2009 09:00:00 24/05/2009 11:00:00

If vDate is set to 21/05/2009 (the actual time part is irrelevant for
this part of the code) then the recordset should return the second
record only because that record is the only one where the start date
is less than or equal to vDate AND the end date is greater than or
equal to vDate. Of course, in practice, the actual date in vDate is
determined by the user to return different records.

When this runs I get the "Data type mismatch in criteria expression."
error on this line of the code. If I change EndDate to EndTime I do
not get the error and the code works OK except, of course, the time
element is included which (probably) means that I will not get the
correct results. I also get the same error if I use DateValue(EndTime)
instead of EndDate although using DateValue(StartTime) does NOT
produce an error.

I don't understand why the EndDate (and DateValue(EndTime)) will
produce an error where StartDate does not. Anyone have any ideas on
why and how to fix it.

Peter Hibbs.

 

[Peter Hibbs]

John,

Interesting. You are right, sort of.

In fact, in the tblDiary table there are numerous records with a NULL
EndTime but these did not show up in my query because the join to the
tblCategories table eliminates them. This is because the data in the
Category field means that any records with a NULL EndTime do not show
up in the results. However, I guess that these records still get
processed in some way and mess up the EndTime field, although I can't
quite see why.

Anyway, your Query worked (had to add a couple of closing brackets on
the DateValue functions but I figured it out).

Thanks for you help, I will go with your solution now.

Peter Hibbs.

My guess is that you have one or more records where ENDTIME contains a null
value. This is almost always the case when I see this type of error.

Try modifying your source query to the following and see if it works:

SELECT tblDiary.StartTime, tblDiary.EndTime
, IIF(IsDate([StartTime]),DateValue([StartTime],Null) AS StartDate
, IIF(IsDate([EndTime]),DateValue([EndTime],Null) AS EndDate
, tblDiary.Notes
, tblCategories.ColorCode, tblCategories.Priority
FROM tblCategories INNER JOIN tblDiary
ON tblCategories.Category = tblDiary.Category
ORDER BY tblDiary.StartTime;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

With Access 2000 I have the following simple query.

SELECT tblDiary.StartTime, tblDiary.EndTime, DateValue([StartTime]) AS
StartDate, DateValue([EndTime]) AS EndDate, tblDiary.Notes,
tblCategories.ColorCode, tblCategories.Priority
FROM tblCategories INNER JOIN tblDiary ON tblCategories.Category =
tblDiary.Category
ORDER BY tblDiary.StartTime;

The table (tblDiary) has a list of appointments where the StartTime
and EndTime fields ALL hold valid date + time values. The StartDate
and EndDate are alias fields which return just the date part of the
start and end times.

In VBA code I have :-

Dim vDate As Date

vDate = Date
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryAppointments
WHERE StartDate <= #" & Format(vDate, "yyyy/m/d") & "# AND EndDate >=
#" & Format(vDate, "yyyy/m/d") & "#")

(All on one line).

The idea is to return all records where the date in vDate falls within
the range of the start date and end date for each record.

For example :-

StartTime EndTime
19/05/2009 09:00:00 19/05/2009 11:00:00
20/05/2009 09:00:00 22/05/2009 11:00:00
23/05/2009 09:00:00 24/05/2009 11:00:00

If vDate is set to 21/05/2009 (the actual time part is irrelevant for
this part of the code) then the recordset should return the second
record only because that record is the only one where the start date
is less than or equal to vDate AND the end date is greater than or
equal to vDate. Of course, in practice, the actual date in vDate is
determined by the user to return different records.

When this runs I get the "Data type mismatch in criteria expression."
error on this line of the code. If I change EndDate to EndTime I do
not get the error and the code works OK except, of course, the time
element is included which (probably) means that I will not get the
correct results. I also get the same error if I use DateValue(EndTime)
instead of EndDate although using DateValue(StartTime) does NOT
produce an error.

I don't understand why the EndDate (and DateValue(EndTime)) will
produce an error where StartDate does not. Anyone have any ideas on
why and how to fix it.

Peter Hibbs.