Record should not be on the Report (1 Viewer)

dcavaiani · Apr 29, 2016

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 · Apr 29, 2016

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 · Apr 29, 2016

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

Minty · Apr 29, 2016

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

dcavaiani · Apr 29, 2016

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 · Apr 29, 2016

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 · Apr 29, 2016

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 · Apr 29, 2016

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

dcavaiani · Apr 29, 2016

Code:

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 · Apr 29, 2016

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

Minty · May 3, 2016

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

Record should not be on the Report (1 Viewer)

dcavaiani

Registered User.

Minty

AWF VIP

dcavaiani

Registered User.

Minty

AWF VIP

dcavaiani

Registered User.

Minty

AWF VIP

dcavaiani

Registered User.

Minty

AWF VIP

dcavaiani

Registered User.

dcavaiani

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread