less than parameter in query works except when value is 100

[fcortes]

Hi

This is a rather odd behaviour

I have a query that checks a table where there's a field that only has numbers from 0 to 100 (a grade), let's call that field "average" (note, the values 0 to 100 are actual numeric values, not percentages)

here's the problem:

when I filter the query using a parameter like <[value] on the average field, the query does show the expected records that have an average value that is less than the value that I input when prompted... except that it also includes the records on which the average field is 100 ... :banghead: ... for some uknown reason.

to clarify:
get this.. it won't show anything over the imput value, it just shows anything under the value I imput (good) and anything that has an average of 100

when I hard code the value for the parameter say <65 the query gives me the results expected (anything less than 65 in the average field) without including records with average equal to 100

some details:

the average field has this code: Average: CInt(Nz([Grade]))

the query looks like this:

SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, CInt(Nz([Grade])), [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
HAVING (((CInt(Nz([Grade])))<[value]));

I'm on access 2007

Any help will be greatly appreciated

 

[bob fitz]

What result do you get using:
< CInt([value])

 

[CJ_London]

if your 'average' field is actual numeric values, why are you using the CInt function?

And why are you grouping when you are not summing or averaging etc? In this situation you would be better to use SELECT DISTINCT instead of GROUP BY

 

[fcortes]

What result do you get using:
< CInt([value])

bob.. when I used your solution I got my answer.. it works now!.. no more records with 100 on the average.. only the intended records

Thank you!

 

[fcortes]

CJ..

thank you for reply as well

I guess at some point the I think I needed to round up the values and Cint() did the job at that time and it hasn't bother me, (it's been a while I put this thing together and just now came to have some time to do some upgrades) but I will look into your suggestions.

the grouping might not apply tecnically for this query but it does make sense on other queries in which I'm averaging periods. since this query is for the first period there's no actual averaging happening, but I see your point and it's a bit misleading on my part.

Thank you again.