Query Parameters

[denileigh]

I have a query with sick hours, vacation hours, bereavement hours and other hours.

I need it to pull up any of those entries IF they are >0.

If I put >0 under each column then nothing comes up because they are not ALL populated.

Do I need an expression in the query???

I'm learning ALOT but still confused.

THANKS!

 

[hair]

If I understood well your problem , try -- WHERE yourfield IS NOT NULL --as condition in the query, if you search the fields that have a value. If the fields are 0 by default, like you set that up in the table, your version should work too(if the fiels is number)

 

[denileigh]

If I understood well your problem , try -- WHERE yourfield IS NOT NULL --as condition in the query, if you search the fields that have a value. If the fields are 0 by default, like you set that up in the table, your version should work too(if the fiels is number)

Hmmm...lemme try to explain it a little better.

I have columns, vacation, holiday, sick and other, etc.

I need the query to show me a report of ANY of those fields that is greater than 0 (the default value).

When I type "is not null" it looks for records in which they are all not null but that won't be true, for example, someone has 8 vacation hours but no sick, other, etc.

 

[Mile-O]

You should remove the repeating group from your table and create a table for these Leave Types and also a junction table to simulate the many-to-many relationship you properly need.

 

[denileigh]

You should remove the repeating group from your table and create a table for these Leave Types and also a junction table to simulate the many-to-many relationship you properly need.

How do you do that without having to re-key in all the 2000+ records in there and reformatting payroll, etc?

Actually, I created a report with 4 subreports and it seems to work okay as a work-a-round except....if the subs have 4 records, the report repeats 4 times,, if it has 14 records, it repeats 14 times....what's up with that?

 

[Jon K]

I have a query with sick hours, vacation hours, bereavement hours and other hours.

I need it to pull up any of those entries IF they are >0.

If I put >0 under each column then nothing comes up because they are not ALL populated.

I think you used ANDs instead of ORs in the query criteria.

Switch the query to SQL View. In the Where Clause, change the ANDs to ORs like this:-

([sick hours] >0 OR [vacation hours] >0 OR [bereavement hours] >0 OR [other hours] >0)

 

[denileigh]

Worked like a charm! THANKS SOOO MUCH!