Query with Criteria Not Working

R

Robert T

Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
 
O

Ofer Cohen

Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"
 
R

Robert T

Hello:

I tried both of those expressions earlier and then I tried it again after
reading your message. I receive the following error message.

You tried to execute a query that does not include the specific expression ………

Ofer Cohen said:
Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"


--
Good Luck
BS"D


Robert T said:
Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
Click to expand...
Click to expand...
 
R

Robert T

I think I should clarify the situation a little. I can run the query and it
will count the number of StudentIDs registered for each class because there's
no criteria in that field. It simply counts all StudentIDs whether their
Attend_Status is "registered", "attended", "no show", or whatever.

The problem occurs whenever I add a criteria, that's when the query fails to
execute and I receive the error message described earlier.

You tried to execute a query that does not include the specific expression ………


Robert T said:
Hello:

I tried both of those expressions earlier and then I tried it again after
reading your message. I receive the following error message.

You tried to execute a query that does not include the specific expression ………

Ofer Cohen said:
Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"


--
Good Luck
BS"D


Robert T said:
Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Can you post the full SQL?

--
Good Luck
BS"D


Robert T said:
I think I should clarify the situation a little. I can run the query and it
will count the number of StudentIDs registered for each class because there's
no criteria in that field. It simply counts all StudentIDs whether their
Attend_Status is "registered", "attended", "no show", or whatever.

The problem occurs whenever I add a criteria, that's when the query fails to
execute and I receive the error message described earlier.

You tried to execute a query that does not include the specific expression ………


Robert T said:
Hello:

I tried both of those expressions earlier and then I tried it again after
reading your message. I receive the following error message.

You tried to execute a query that does not include the specific expression ………

Ofer Cohen said:
Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"


--
Good Luck
BS"D


:

Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
Click to expand...
Click to expand...
Click to expand...
 
R

Robert T

SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered, Count(tblLink.Attend_Status) AS
CountOfAttend_Status
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#) AND
((Count(tblLink.Attend_Status))="attended" Or
(Count(tblLink.Attend_Status))="attended-dnr"))
ORDER BY tblClasses.Class_Date;


Ofer Cohen said:
Can you post the full SQL?

--
Good Luck
BS"D


Robert T said:
I think I should clarify the situation a little. I can run the query and it
will count the number of StudentIDs registered for each class because there's
no criteria in that field. It simply counts all StudentIDs whether their
Attend_Status is "registered", "attended", "no show", or whatever.

The problem occurs whenever I add a criteria, that's when the query fails to
execute and I receive the error message described earlier.

You tried to execute a query that does not include the specific expression ………


Robert T said:
Hello:

I tried both of those expressions earlier and then I tried it again after
reading your message. I receive the following error message.

You tried to execute a query that does not include the specific expression ………

:

Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"


--
Good Luck
BS"D


:

Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Why is the criteria under the count?

Try

SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered, Count(tblLink.Attend_Status) AS
CountOfAttend_Status
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (tblClasses.Class_Date Between #11/1/2006# And #4/30/2007# )
AND tblLink.Attend_Status In ("attended", "attended-dnr")
ORDER BY tblClasses.Class_Date


--
Good Luck
BS"D


Robert T said:
SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered, Count(tblLink.Attend_Status) AS
CountOfAttend_Status
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#) AND
((Count(tblLink.Attend_Status))="attended" Or
(Count(tblLink.Attend_Status))="attended-dnr"))
ORDER BY tblClasses.Class_Date;


Ofer Cohen said:
Can you post the full SQL?

--
Good Luck
BS"D


Robert T said:
I think I should clarify the situation a little. I can run the query and it
will count the number of StudentIDs registered for each class because there's
no criteria in that field. It simply counts all StudentIDs whether their
Attend_Status is "registered", "attended", "no show", or whatever.

The problem occurs whenever I add a criteria, that's when the query fails to
execute and I receive the error message described earlier.

You tried to execute a query that does not include the specific expression ………


:

Hello:

I tried both of those expressions earlier and then I tried it again after
reading your message. I receive the following error message.

You tried to execute a query that does not include the specific expression ………

:

Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"


--
Good Luck
BS"D


:

Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
Click to expand...
Click to expand...
Click to expand...
 
R

Robert T

Hello Ofer:

Thanks for your patience and assistance so far.

I placed the criteria under the count because in that particular case, I
want to count the number of students who actually attended the class. In the
previous column, I wanted to count the number of students who attended the
class. In other words, I need to show two things:

[1] how many students were registered for each class and then
[2] count how many of them actually attended the class ["attended" or
"attended-dnr"].

The Attend_Status field is initially "Registered". However, if they attend
the class, it's changed to "Attended" or "Attended-DNR".

I tried your SQL but I received the same error message.

You tried to execute a query that does not include the specific expression
................ as part of an aggregte function.

Robert

Ofer Cohen said:
Why is the criteria under the count?

Try

SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered, Count(tblLink.Attend_Status) AS
CountOfAttend_Status
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (tblClasses.Class_Date Between #11/1/2006# And #4/30/2007# )
AND tblLink.Attend_Status In ("attended", "attended-dnr")
ORDER BY tblClasses.Class_Date


--
Good Luck
BS"D


Robert T said:
SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered, Count(tblLink.Attend_Status) AS
CountOfAttend_Status
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#) AND
((Count(tblLink.Attend_Status))="attended" Or
(Count(tblLink.Attend_Status))="attended-dnr"))
ORDER BY tblClasses.Class_Date;


Ofer Cohen said:
Can you post the full SQL?

--
Good Luck
BS"D


:

I think I should clarify the situation a little. I can run the query and it
will count the number of StudentIDs registered for each class because there's
no criteria in that field. It simply counts all StudentIDs whether their
Attend_Status is "registered", "attended", "no show", or whatever.

The problem occurs whenever I add a criteria, that's when the query fails to
execute and I receive the error message described earlier.

You tried to execute a query that does not include the specific expression ………


:

Hello:

I tried both of those expressions earlier and then I tried it again after
reading your message. I receive the following error message.

You tried to execute a query that does not include the specific expression ………

:

Try
tblLink.Attend_Status In( "Attended" , "Attended-DNR")

Or try
tblLink.Attend_Status = "Attended" Or tblLink.Attend_Status = "Attended-DNR"


--
Good Luck
BS"D


:

Hello:

I'm trying to build a query that counts the number of students who
registered for our classes between two dates, and I also need to count the
number of students who actually attended the class. I can get the first one
[registered] easily, however, whenever I add another field to the query which
has a criteria, I receive a Data Mis-Match error. Obviously I'm doing
something wrong. That extra field that causes the Data Mis-Match error is:

Attended: StudentID
Count
tblLink.Attend_Status = "Attended" or "Attended-DNR"

I'm posting the query below that works.


Thanks,
Robert

Note: The following query works, giving me the number of students who
registered.


SELECT tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location,
Count(tblLink.StudentID) AS Registered
FROM tblClasses INNER JOIN tblLink ON tblClasses.ClassNo = tblLink.ClassNo
GROUP BY tblClasses.CourseName, tblClasses.ClassNo, tblClasses.Class_Date,
tblClasses.Class_Time, tblClasses.CourseNo, tblClasses.Class_Location
HAVING (((tblClasses.Class_Date)>#10/31/2006# And
(tblClasses.Class_Date)<#5/1/2007#))
ORDER BY tblClasses.Class_Date;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Students Who Attended Class? 3
Count Yes/No with Criteria 2
How do I count non-duplicated records in a query in Access? 1
0uery criteria not working 1
'Data Type Mismatch In Criteria Expression' error when using 'Count' in query Design; Help! 0
unmatched query not working 2
Query selecting NOT Attended 10
Query criteria 15

Top