Parameter query not returning expected results

[terrytek]

I am trying to run a parameter query to return results where one of the fields (TotalHours) is greater than or equal to the parameter. If I run the query without the parameter, it gives the expected values, but when I run it with the parameter, it is returning only some of the records, but they have nothing to do with the parameter value--some of them are above the value, some below, and some records that should be returned are not.

SELECT qryStudentTotalHoursSinceLastTest.StudentID, qryStudentTotalHoursSinceLastTest.FirstName, qryStudentTotalHoursSinceLastTest.LastName, qryStudentTotalHoursSinceLastTest.TotalHours, qryStudentTotalHoursSinceLastTest.LastOfTestDate
FROM qryStudentTotalHoursSinceLastTest
WHERE (((qryStudentTotalHoursSinceLastTest.TotalHours)>=[Student hours >=___ hours since last test]));

I tried removing the ">=___" from the parameter prompt in case one of them was a reserved character, but it made no difference.

Any clue why this is happening?

Is this a WHERE, HAVING, GROUP BY problem? In the Access query design window, you cannot group by only one field if you show the Totals row; you have to have a value in that row for every field, and the default is GROUP BY. When troubleshooting another query, a member here told me I didn't have to do all those GROUP BYs, but I don't understand how you cannot if you use the Totals row in the query design window. This has made me really confused about the use of GROUP BY.

I even tried the query using GROUP BY on all the fields with
>=[Student hours >=___ hours since last test] in the criteria field for TotalHours (which results in a HAVING clause instead of a WHERE clause) with no joy.

SQL is hard

Thanks.

 

[pbaldy]

Could the hours field be text? Does it look like you're getting an alphabetical result rather than a numeric one?

 

[plog]

SQL is hard

Agreed. This issue is really a sneaky one too. It has to do with comparison methods. There are 3 ways to compare data--like text, like numbers, and like dates (which is really numbers as well).

Check out this data:

Field1
1
2
5
11
291

That set of data is sorted like they are numbers. Pretty obvious. But suppose you converted that field to text data type and resorted it. It would look like this:

Field1
1
11
2
291
5

As text 11 comes before 2 because it looks at the firsth character, sees that 1<2 and stops comparing there. That's what's happening with your data.

WHERE (((qryStudentTotalHoursSinceLastTest.TotalHours)>=[Student hours >=___ hours since last test]));

When user's input data (i.e. when you have them enter via a prompt), Access considers them strings and compares them like strings. If you input 9 into the prompt, only TotalHours values who's first character is 9 will be returned--no matter what digits follow.

That's the default method of Access when you use inputs like that. To get around it you have to explicitly tell the system its a number and to compare them as such. To do this you would use one of the Data Conversion Functions of Access (https://www.techonthenet.com/access/functions/) around the user input in the SQL. I would suggest CDbl(https://www.techonthenet.com/access/functions/datatype/cdbl.php) to allow for decimals:

WHERE Cdbl(qryStudentTotalHoursSinceLastTest.TotalHours)>=CDbl([Student hours >=___ hours since last test]);