query criteria

[lipin]

What is the syntax for the criteria if I want to show those records where the Hours field is not equal to 0.00 or 40.00? I keep getting a data mismatch error.

 

[David R]

Criteria: <> 0 And <> 40

 

[lipin]

Tried it David, but it is still showing records with 40. It got rid of the 0s though.

 

[David R]

I don't know why it would. I just tested it to be sure and this worked in the criteria for a numeric field: <> 200 And <>150

 

[RichMorrison]

Just a guess:
is the data field "single" or "double" with 2 decimal places ? Looks like yes from your example.

If so, maybe <>0 And <>40.00

RichM

 

[lipin]

The query in question is the last one in a series. In the two tables involved, the field is numeric "single", format "standard", decimal places "2". In all the queries the format is standard , and dec places 2. Except in the union query, but the union works fine so the fields must be the same at that point. And in the three queries after the union query there is no problem with the data.
But I want the last query to show only the records of employees that don't add up to a 40 hr week.

I have tried all the criteria suggestions that were submitted and then some. Does anyone have any idea why it insists on showing all records regardless of criteria? I am out of ideas.
(It is a summed field, if that makes a difference??)

Thank you.
lip

have tried:
<>0 And <>40.00
<>0 And <>40
Not 40.00
<>40

 

[David R]

I think I might understand now.

If you put criteria in a summed field, it will apply that criteria to each entry to determine whether it needs to add it to the sum or not. Since no particular day has 40 hours of work in it, it doesn't block any of those entries. However since all the days in a 0.00 total week have 0 hours, it blocks those effectively.

On the other hand, if you put the criteria on the SumOfHours field in the next query, it will block out those entries where the Sum is 0 or 40.

Make more sense now?

 

[lipin]

Thank you David. That clears things up a bit.

 

[lipin]

Ok, this is what I tried as select query:

SELECT [cost cntr],[clock #],[sumofHours]
from [balanced to 40]
where ([sumofHours] <>40.00)

And this is part of what it returned:
Why are the records with 40 hrs returned???
This is not the query where the hours are actually summed.
That action is done in the prefious query. This is just a final query that is supposed to show all the records that don't have 40 in the summed hours field.
(the format is standard, with 2 dec places)

cost cntr clock # sumofHours
1 230 40.00
300 871 33.50
7 1203 40.00
15 1627 40.00
65 1631 38.17
300 1700 24.00
13 1705 40.00
300 1724 39.00
7 1906 40.00
300 2104 37.00
1 2147 40.00
13 2246 40.00
13 2318 40.00
13 2343 40.00
3 2348 40.00
7 2374 35.47
1 2410 40.00