NO ZEROS IN MY REPORT

[gearhead03]

I AM DESIGNING A REPORT THAT IS AN INVOICE. THE FIELDS INVOLVED ARE; CHARGES1, CHARGES2, CHARGES3, CHARGES4. FIELD TYPE IS CURRENCY. I HAVE THE DEFAULT SET TO ZERO BECAUSE THE FORM AND REPORT HAVE A CALCULATED FIELD TOTALCHARGES. MY PROBLEM IS THIS; WHEN CHARGES1, CHARGES2, CHARGES3, OR CHARGES4 IS ZERO I DO NOT WANT THE ZERO TO PRINT ON THE REPORT.
INSTEAD OF:
PARTS $20.00
LABOR $10.00
$0.00
$0.00
TOTAL $30.00

I WANT:
PARTS $20.00
LABOR $10.00


TOTAL $30.00

 

[dhookom]

Your base problem is having repeating groups of fields. This isn't normalized and will cause problems like this until you change your table structure. Each separate charge should create a new record in a related table.


Duane
MS Access MVP

 

[scriverb]

dhookom, is correct in that you should normalize your database. This requires some significant thought because it will affect you input forms and many other design changes in existing queries, forms, reports, etc. Until that time you can get buy by creating a normalized file of your charges using a UNION query. Use the following to accomplish what you would like in your report.

1. Create a UNION query using the following SQL:
SELECT tblCharges.ID, tblCharges.Charges1 as Charges, 1 as Charges_Sort
FROM tblCharges
WHERE (((tblCharges.Charges1)<>0))
ORDER BY tblCharges.ID
UNION
SELECT tblCharges.ID, tblCharges.Charges2 as Charges, 2 as Charges_Sort
FROM tblCharges
WHERE (((tblCharges.Charges2)<>0))
UNION
SELECT tblCharges.ID, tblCharges.Charges3 as Charges, 3 as Charges_Sort
FROM tblCharges
WHERE (((tblCharges.Charges3)<>0))
UNION
SELECT tblCharges.ID, tblCharges.Charges4 as Charges, 4 as Charges_Sort
FROM tblCharges
WHERE (((tblCharges.Charges4)<>0));

Now save that query and let's name it qryNormalizedCharges.

2. Create a select query that takes in as its input recordset the above query. Save and Name it qryCharges. Use the following SQL to accomplish this:
SELECT A.ID, A.Charges, A.Charges_Sort
FROM qryNormalizedCharges as A
ORDER BY A.ID, A.Charges_Sort;

This query resorts the charges in ID and Charges# order.

3. Create a new query that links your original table tblCharges to this last query qryCharges using the ID field as the common link. This is probably your Invoice# in your table. I have used tblCharges and ID as the table name and field name. They will have to be modified throughout the SQL to match yours. You never mentioned the actual names of these.
SELECT tblCharges.ID, qryCharges.Charges
FROM tblCharges LEFT JOIN qryCharges ON tblCharges.ID = qryCharges.ID;

Save and name this query qryReportCharges. Now use this last query as the RecordSource for your Report. There are no zero values here, the records are sorted properly, and I believe you can now rollup the field Charges in your Total control.

Post back if you have any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???