Trouble with Formulas in 2010 (1 Viewer)

Hello,

Thanks for reading this. First question is it possible to make a report so complex that Access can not handle all of the formulas?

Second and more important. We have a report based on this query:
SELECT Tasks.ID AS TaskNo, Customers.[Customer Number] AS CustomerNumber, Customers.Notes, Customers.Notes2, Customers.Company, Tasks.*, Projects.Origin AS Origin, Customers.CustomerName AS CustName, Projects.FSC, Projects.FSCPercent, Projects.Destination AS Destination, Projects.Category AS Category, Projects.EmployeeRate AS EmployeeRate, Projects.ProjectName AS ProjectName, Projects.CustomerRate, Users.FullName AS UserFullName, Customers.Terms, Projects.PO_Number
FROM Customers, Projects, Tasks, Users
WHERE ((([Projects]![CustomerID])=[Forms]![Demo]![cmbCustomerName] And ([Projects]![CustomerID])=[Customers]![ID]) AND (([Projects]![ID])=[Tasks]![ProjectID]) AND (([Tasks]![AssignedToUserID])=[Users]![ID]) AND (([Tasks]![Status])='10'));

in the detail section this formula works. =IIf([Tons]>0,([Tons]*[CustomerRate]),IIf([Yards]>0,([Yards]*[CustomerRate]),IIf([Hours]>0,([Hours]*[CustomerRate]),IIf([Gross]>0,(([Gross]-[Tare])/2000)*[CustomerRate],[CustomerRate]))))

This is an invoice and we need to be able to create a subtotal which adds the line in the detail section.

To multiply the subtotal by [FSCPercent] and then add those together along with sum([FSC]) to create a grand total.

There is another issue, but this one is the biggest. My effort lead to inaccurate and inconsistent results apparently depending upon the customer selected.

Any other details I can provide or a link to a Dropbox folder with the application. Mike

Yes! - but can usually be overcome by simplifying (e.g. create temporary tables). You will also get the error 'query is too complex' or similar if there are unhandled nulls in the data (either eliminate them or use Nz)

Re subtotals

1. Put the formula in the detail section into your query and call it say Result
2. In the form, change to controlsource for your formula control to Result - and ensure the control name is the same name.
3. In the form footer create your subtotal control (lets call it InvValue)and in its controlsource put =sum([Result])
4. In the formfooter create a new control called FSCValue and it's control source is =[FSCPercent]*[InvValue]
5. In the formfooter create a new control called InvTotal and it's control source is =[FSCValue]+[InvValue]

Note if these are currencies, then to resolve decimals and rounding, you may want to use th CCur function to convert your values to currency

Steps 1 and 2 work great. Step 3 displayed on the first test before I added the Next two steps, now step 4 is visible on reports with an FSCPercent and is accurate. The property sheet for the Text Box called InvValue and the section of the form.

Yahoo! If a part of the equation is Null, will the formula not display? To calculate the Invoice Total in addition to what you gave me, this seems to be working =IIf([FSCPercentValue]>0,[FSCPercentValue]+[InvValue],IIf([FSCValue]>0,[FSCValue]+[InvValue],[InvValue])) !! My Text Box with the Name InvValue and the Control Source =Sum([Result]) does not display on the report, so I made another text box to display the [InvValue] and we have a working invoice!! CJ U Da Man!!

If a part of the equation is Null, will the formula not display?

Click to expand...

Correct, use the Nz function on each variable - ie. =nz(a)/nz(b) and not nz(a/b)

Glad you got it working