Record should not be on the Report

[dcavaiani]

I have a report which ONLY shows records where the Estimated amount is LESS THAN the corresponding ACTUAL amount, on ANY 1 of the 4 comparable fields.

I have the record below appearing on the report and I cannot figure out why?

esthours EstHoursTotal EstMatlTotal estjobTotal
1175.42 52894 -882 52011
jobhours joblabor Jobmaterial jobtotal
1175.42 52894 -882 52011

 

[Minty]

Did you use less than < or less than or equal <= in your criteria?
You also have a negative amount in your sample data - that will require the reverse logic.

 

[dcavaiani]

I used just the < and the material amount is EQUAL even though negative in both fields ??

 

[Minty]

Are the figures you are showing calculated / rounded for display purposes? If they are you may not be comparing exactly the same values?

 

[dcavaiani]

The numbers have been rounded before but the fields in the base table are all defined and populated with INSERT INTO - as double , currency and 0 decimals, except for hours which is fixed and 2 decimals?

 

[Minty]

Have you actually copied and pasted the stored values you are comparing into Excel - I'd bet they aren't what you expect, or you are comparing a rounded value with the original value without realising it. Debug.print the values in code.

 

[dcavaiani]

cust job# jobstart esthours EstHoursTotal EstMatlTotal EstSubTotal estjobTotal jobhours joblabor Jobmaterial jobtotal
Prange 5 30-Oct-14 1175.41 $52,893.00 ($882.00) $0.00 $52,011.00 1175.41 $52,893.00 ($882.00) $52,011.00

Pasted into and copied from Excel shown above

 

[Minty]

Remove the currency formatting, it stores more that you can see.

 

[dcavaiani]

INSERT INTO jobalerts ( cust, [job#], jobstart, esthours, esthourstotal, estmatltotal, estsubtotal, estjobtotal, jobhours, joblabor, jobmaterial, jobtotal )
SELECT jobs.cust, jobs.[job#], jobs.jobstart, jobs.esthours, jobs.EstHoursTotal, jobs.EstMatlTotal, jobs.EstSubTotal, jobs.estjobTotal, invoicehistory2.jobhours, invoicehistory2.joblabor, (invoicehistory2.jobmaterial+invoicehistory2.joboverhead) AS Jobmaterial, invoicehistory2.jobtotal
FROM jobs, invoicehistory2
WHERE ((jobs.Cust=invoicehistory2.customer And jobs.[job#]=invoicehistory2.job) And ((jobs.estJobTotal<invoicehistory2.jobtotal) Or (jobs.esthourstotal<joblabor) Or (jobs.esthours<invoicehistory2.jobhours) Or (jobs.estmatltotal<Jobmaterial)));

Sql which populates the table that report print from. The jobs table has the estimates and the invoicehistory2 has the actuals.

 

[dcavaiani]

Not sure why, but it is the negative field comparison which is throwing this off.

 

[Minty]

Have a look ad this http://www.tek-tips.com/viewthread.cfm?qid=1011721 (its from excel but might help)