Type mismatch in a criteria that used to work

[RobD]

I have a query that calculates a shift number by looking at the start time of
an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch. No
matter what I try. I have tried to force a CStr, Cint (by changing 1st to
1), but nothing seems to make a difference.

 

[Bob Barrows [MVP]]

I have a query that calculates a shift number by looking at the start
time of an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch.
No matter what I try. I have tried to force a CStr, Cint (by
changing 1st to 1), but nothing seems to make a difference.

Show us the sql that is failing (switch your query to SQL View either
via the View menu or the toolbar button or the right-click context
menu).
I'm particularly interested in the WHERE clause but the SELECT clause
should be of interest as well ... especially the part where this
calculation is done.

 

[Jerry Whittle]

What is the criteria that you are attempting to use?

 

[RobD]

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION, Left([description],6) AS
linetype, [downtime 1].[reason code], [downtime 1].comments, [downtime
1].duration, [downtime 1].dtstart, [downtime 1].dtfinish, [downtime
1].dtevent, [downtime 1].[start time], [downtime 1].[finish time], [downtime
1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58] Is Not
Null,[Forms]![Downtime Report]![Combo58],nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it out, but
fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that calculates a shift number by looking at the start time of
an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch. No
matter what I try. I have tried to force a CStr, Cint (by changing 1st to
1), but nothing seems to make a difference.

 

[Bob Barrows [MVP]]

Let's first figure out what values we need to supply in the criterion to
make this work. Based on your prior post, I would say to start with:

WHERE [downtime 1].shift = "1st"

Try it. does it work? Try "2nd" and "3rd". Do they work?

Now you know what values need to be supplied, so now you can concentrate
on the values being returned by Combo58. Does it return the appropriate
values?

Lastly, that wildcard bit ... wildcards only work with the LIKE
operator. i would prefer to do this:

WHERE ([downtime 1].shift =[Forms]![Downtime Report]![Combo58]
OR Forms]![Downtime Report]![Combo58] Is Null)

I'm not sure what that nz([shift] expression is referring to ...

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION,
Left([description],6) AS linetype, [downtime 1].[reason code],
[downtime 1].comments, [downtime 1].duration, [downtime 1].dtstart,
[downtime 1].dtfinish, [downtime 1].dtevent, [downtime 1].[start
time], [downtime 1].[finish time], [downtime 1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58]
Is Not Null,[Forms]![Downtime Report]![Combo58] ,nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it
out, but fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that calculates a shift number by looking at the
start time of an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type
mismatch. No matter what I try. I have tried to force a CStr,
Cint (by changing 1st to 1), but nothing seems to make a difference.

 

[RobD]

I like the simplified code, I will need to change this in my criteria. The
only catch is that even when I criteria "1st" or 1st or 1 in the case where I
force an integer format, I get a type mismatch. I have also tried to force a
string type in the shift calculation at the conditional's result level (ie.
CStr("1st") instead of "1st") and also at the defintion level (ie.
CStr(IIf....)).

I am trying to run this just from the query without the form right now, so I
know that the form is not the issue.

Let's first figure out what values we need to supply in the criterion to
make this work. Based on your prior post, I would say to start with:

WHERE [downtime 1].shift = "1st"

Try it. does it work? Try "2nd" and "3rd". Do they work?

Now you know what values need to be supplied, so now you can concentrate
on the values being returned by Combo58. Does it return the appropriate
values?

Lastly, that wildcard bit ... wildcards only work with the LIKE
operator. i would prefer to do this:

WHERE ([downtime 1].shift =[Forms]![Downtime Report]![Combo58]
OR Forms]![Downtime Report]![Combo58] Is Null)

I'm not sure what that nz([shift] expression is referring to ...

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION,
Left([description],6) AS linetype, [downtime 1].[reason code],
[downtime 1].comments, [downtime 1].duration, [downtime 1].dtstart,
[downtime 1].dtfinish, [downtime 1].dtevent, [downtime 1].[start
time], [downtime 1].[finish time], [downtime 1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58]
Is Not Null,[Forms]![Downtime Report]![Combo58] ,nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it
out, but fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query that calculates a shift number by looking at the
start time of an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type
mismatch. No matter what I try. I have tried to force a CStr,
Cint (by changing 1st to 1), but nothing seems to make a difference.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Jerry Whittle]

I like Bob's idea of simplifying things, but I would go a step further by
taking out the criteria completely and seeing if there is still a problem.

For example "Date" is a reserved word which you really shouldn't use as a
table, query, or field name. You can usually get away with it if you put
square brackets around it like so: [Date] .

Also it's possible that the Left function is acting up for some reason.

SELECT [downtime 1].Date,
[downtime 1].DESCRIPTION,
Left([description],6) AS linetype,
[downtime 1].[reason code],
[downtime 1].comments,
[downtime 1].duration,
[downtime 1].dtstart,
[downtime 1].dtfinish,
[downtime 1].dtevent,
[downtime 1].[start time],
[downtime 1].[finish time],
[downtime 1].shift ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION, Left([description],6) AS
linetype, [downtime 1].[reason code], [downtime 1].comments, [downtime
1].duration, [downtime 1].dtstart, [downtime 1].dtfinish, [downtime
1].dtevent, [downtime 1].[start time], [downtime 1].[finish time], [downtime
1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58] Is Not
Null,[Forms]![Downtime Report]![Combo58],nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it out, but
fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that calculates a shift number by looking at the start time of
an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch. No
matter what I try. I have tried to force a CStr, Cint (by changing 1st to
1), but nothing seems to make a difference.

 

[Bob Barrows [MVP]]

I will need to see the current sql for the [downtime 1] query (actually,
all I need is the defintition for that [shift] field if it's different
from your original post) as well as the sql for the attempt to hardcode
the criterion that fails.
"format" implies a string value so you would need the quotes around the
criterion value in all three cases. If you say you are trying to force
an integer datatype output from the iif, then that is different and I
would need to see how you did it. Here is how I would do it:

IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14, 1,IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22, 2, 3))

I like the simplified code, I will need to change this in my
criteria. The only catch is that even when I criteria "1st" or 1st
or 1 in the case where I force an integer format, I get a type
mismatch. I have also tried to force a string type in the shift
calculation at the conditional's result level (ie. CStr("1st")
instead of "1st") and also at the defintion level (ie. CStr(IIf....)).

I am trying to run this just from the query without the form right
now, so I know that the form is not the issue.

Let's first figure out what values we need to supply in the
criterion to make this work. Based on your prior post, I would say
to start with:

WHERE [downtime 1].shift = "1st"

Try it. does it work? Try "2nd" and "3rd". Do they work?

Now you know what values need to be supplied, so now you can
concentrate on the values being returned by Combo58. Does it return
the appropriate values?

Lastly, that wildcard bit ... wildcards only work with the LIKE
operator. i would prefer to do this:

WHERE ([downtime 1].shift =[Forms]![Downtime Report]![Combo58]
OR Forms]![Downtime Report]![Combo58] Is Null)

I'm not sure what that nz([shift] expression is referring to ...

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION,
Left([description],6) AS linetype, [downtime 1].[reason code],
[downtime 1].comments, [downtime 1].duration, [downtime 1].dtstart,
[downtime 1].dtfinish, [downtime 1].dtevent, [downtime 1].[start
time], [downtime 1].[finish time], [downtime 1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58]
Is Not Null,[Forms]![Downtime Report]![Combo58] ,nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it
out, but fails no matter what I put in it.

:

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query that calculates a shift number by looking at the
start time of an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15
And 22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type
mismatch. No matter what I try. I have tried to force a CStr,
Cint (by changing 1st to 1), but nothing seems to make a
difference.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[John Spencer]

SELECT [downtime 1].Date
, [downtime 1].DESCRIPTION
, Left([description],6) AS linetype
, [downtime 1].[reason code]
, [downtime 1].comments
, [downtime 1].duration
, [downtime 1].dtstart
, [downtime 1].dtfinish
, [downtime 1].dtevent
, [downtime 1].[start time]
, [downtime 1].[finish time]
, [downtime 1].shift
FROM [downtime 1]
WHERE [downtime 1].shift=[Forms]![Downtime Report]![Combo58] OR
[Forms]![Downtime Report]![Combo58] is Null

I suspect that the error is occuring in the DownTime 1 query.

Try the following expression:

IIf(IsDate([Start Time]),Null
, IIF(Hour([Start Time]) Between 7 And 14, 1,
IIf(Hour([start time]) Between 15 And 22, 2, 3))

Is Start Time a dateTime field or is it a string that looks like a time?
IF the latter you could have data that cannot be interpreted as a time
and therefore is causing an error. ISDATE will test the value in Start
Time and make sure it is a valid DateTime or a string that can be
interpreted as a valid DateTime.

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

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION, Left([description],6) AS
linetype, [downtime 1].[reason code], [downtime 1].comments, [downtime
1].duration, [downtime 1].dtstart, [downtime 1].dtfinish, [downtime
1].dtevent, [downtime 1].[start time], [downtime 1].[finish time], [downtime
1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58] Is Not
Null,[Forms]![Downtime Report]![Combo58],nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it out, but
fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query that calculates a shift number by looking at the start time of
an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch. No
matter what I try. I have tried to force a CStr, Cint (by changing 1st to
1), but nothing seems to make a difference.

 

[Bob Barrows [MVP]]

SELECT [downtime 1].Date
, [downtime 1].DESCRIPTION
, Left([description],6) AS linetype
, [downtime 1].[reason code]
, [downtime 1].comments
, [downtime 1].duration
, [downtime 1].dtstart
, [downtime 1].dtfinish
, [downtime 1].dtevent
, [downtime 1].[start time]
, [downtime 1].[finish time]
, [downtime 1].shift
FROM [downtime 1]
WHERE [downtime 1].shift=[Forms]![Downtime Report]![Combo58] OR
[Forms]![Downtime Report]![Combo58] is Null

I suspect that the error is occuring in the DownTime 1 query.

That was my first thought, except he said the query runs fine without
the criterion.

 

[John Spencer]

I have seen that behavior in the past. Query seems to run fine UNTIL
you apply criterion, but when you do, it blows up.

Since applying the criteria against the calculated value means that
every record is touched and one record with an error seems to be all you
need to "blow" the query out of the water.

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

SELECT [downtime 1].Date
, [downtime 1].DESCRIPTION
, Left([description],6) AS linetype
, [downtime 1].[reason code]
, [downtime 1].comments
, [downtime 1].duration
, [downtime 1].dtstart
, [downtime 1].dtfinish
, [downtime 1].dtevent
, [downtime 1].[start time]
, [downtime 1].[finish time]
, [downtime 1].shift
FROM [downtime 1]
WHERE [downtime 1].shift=[Forms]![Downtime Report]![Combo58] OR
[Forms]![Downtime Report]![Combo58] is Null

I suspect that the error is occuring in the DownTime 1 query.

That was my first thought, except he said the query runs fine without
the criterion.

 

[RobD]

I tried the new code before I looked, but the way the previous query works
(downtime 1) is like this:

SELECT [downtime -1].Date, [downtime -1].DESCRIPTION, [downtime -1].[reason
code], [downtime -1].comments, [downtime -1].duration, [downtime -1].dtstart,
[downtime -1].dtfinish, [downtime -1].dtevent, Format([downtime
-1].dtstart,"Long Time") AS [start time], Format([downtime -1].dtfinish,"Long
Time") AS [finish time], IIf(DateDiff("h",#12/30/1899#,[start time]) Between
7 And 14,"1st",IIf(DateDiff("h",#12/30/1899#,[start time]) Between 15 And
22,"2nd","3rd")) AS shift
FROM [downtime -1];

The shift field calculates great everytime. I even tried to take out the
text "1st", 2nd", etc. and replace with integers, but that didn't help either.

SELECT [downtime 1].Date
, [downtime 1].DESCRIPTION
, Left([description],6) AS linetype
, [downtime 1].[reason code]
, [downtime 1].comments
, [downtime 1].duration
, [downtime 1].dtstart
, [downtime 1].dtfinish
, [downtime 1].dtevent
, [downtime 1].[start time]
, [downtime 1].[finish time]
, [downtime 1].shift
FROM [downtime 1]
WHERE [downtime 1].shift=[Forms]![Downtime Report]![Combo58] OR
[Forms]![Downtime Report]![Combo58] is Null

I suspect that the error is occuring in the DownTime 1 query.

Try the following expression:

IIf(IsDate([Start Time]),Null
, IIF(Hour([Start Time]) Between 7 And 14, 1,
IIf(Hour([start time]) Between 15 And 22, 2, 3))

Is Start Time a dateTime field or is it a string that looks like a time?
IF the latter you could have data that cannot be interpreted as a time
and therefore is causing an error. ISDATE will test the value in Start
Time and make sure it is a valid DateTime or a string that can be
interpreted as a valid DateTime.

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

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION, Left([description],6) AS
linetype, [downtime 1].[reason code], [downtime 1].comments, [downtime
1].duration, [downtime 1].dtstart, [downtime 1].dtfinish, [downtime
1].dtevent, [downtime 1].[start time], [downtime 1].[finish time], [downtime
1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58] Is Not
Null,[Forms]![Downtime Report]![Combo58],nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it out, but
fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query that calculates a shift number by looking at the start time of
an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch. No
matter what I try. I have tried to force a CStr, Cint (by changing 1st to
1), but nothing seems to make a difference.

 

[RobD]

I just tried to rework this query with your suggestions. I renamed the Date
field to downdate in the Downtime 1 query, and deleted the field with the
left function callout, but there was no change. I am testing the query using
only the criteria for shift as "1st".

I like Bob's idea of simplifying things, but I would go a step further by
taking out the criteria completely and seeing if there is still a problem.

For example "Date" is a reserved word which you really shouldn't use as a
table, query, or field name. You can usually get away with it if you put
square brackets around it like so: [Date] .

Also it's possible that the Left function is acting up for some reason.

SELECT [downtime 1].Date,
[downtime 1].DESCRIPTION,
Left([description],6) AS linetype,
[downtime 1].[reason code],
[downtime 1].comments,
[downtime 1].duration,
[downtime 1].dtstart,
[downtime 1].dtfinish,
[downtime 1].dtevent,
[downtime 1].[start time],
[downtime 1].[finish time],
[downtime 1].shift ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL:

SELECT [downtime 1].Date, [downtime 1].DESCRIPTION, Left([description],6) AS
linetype, [downtime 1].[reason code], [downtime 1].comments, [downtime
1].duration, [downtime 1].dtstart, [downtime 1].dtfinish, [downtime
1].dtevent, [downtime 1].[start time], [downtime 1].[finish time], [downtime
1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)=IIf([Forms]![Downtime Report]![Combo58] Is Not
Null,[Forms]![Downtime Report]![Combo58],nz([shift],"*"))));


The failure is in the WHERE statement...this will work if I take it out, but
fails no matter what I put in it.

What is the criteria that you are attempting to use?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a query that calculates a shift number by looking at the start time of
an event: shift:
IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 7 And
14,"1st",IIf(DateDiff("h",#12:00:00 AM#,[start time]) Between 15 And
22,"2nd","3rd"))

Now, if I try to criteria against this output, I get a type mismatch. No
matter what I try. I have tried to force a CStr, Cint (by changing 1st to
1), but nothing seems to make a difference.

 

[RobD]

Now I have a new problem...when I run the query I get the "This expression is
typed incorrectly, or it is too complex to be evaluated." message.

Here is the SQL:

Select......,[downtime 1].shift
FROM [downtime 1]
WHERE ((([downtime 1].shift)="1st"));