Suppress zeros in query.

[kkershaw]

I don't know if I should post this in the query or the report forum, but here goes:

My report shows a truck and its miles and fuel by state: Example

Truck 119 IDMiles IDFuel MtMiles MtFuel

I have too many states to fit on one landscaped sheet of paper so I want the report/query to suppress those states that have zeroes in them. I tried using >0 in the criteria field of the query but that didn't work. Is this possible?

Thanks.

 

[Jack Cowley]

If each state is a separate field in a record instead of a separate record this my be difficult to do. A bit more detail on how your table is set up may help someone here to help you find a solution.

 

[kkershaw]

Each state is a separate field in a record.

The table comes from a paper form where the driver fills in boxes for the state he's traveled in and the gallons of fuel he purchased in each state. My table looks something like this:
Ticket #, Trailer #, IDMiles, ID Fuel, MtMiles, MTFuel, Commodity, Tons, etc.

Thanks

 

[Jack Cowley]

I would suggest that you create a One to Many relationship here. You need to create it so you have, let's say, Driver, Ticket # and any other data that would be appropriate for your main table. Then create another one where for each State, mileage in the State and whatever is a single record. Now you will be able to easily find which States he/she was in because the others will not be listed.

I hope that made sense....

 

[kkershaw]

Thanks. That will work. I was afraid I would have to break my one main table into smaller ones, and I see the wisdom of that now.

You people are great!