Calculating totals in a report

[pbaldy]

I'm saying to change the name of that textbox, if it's currently "Customer Name".



Best to take care of the wife though! If you're still stuck tomorrow, can you post the db here?

[Jamescdawson]

Morning Paul,

As per your suggestion, I tried a few options and alternatives around the text box issue but still can't get it working. The best I get is the #error in the Totals field on each page of the report.

I've uploaded the front and back ends as separate files. The .be usually sits on a server. The .fe links may therefore need tweeked.

The back end has got test customer data in it. The waste data is Live and proper

I tried changing the text box to txtcustomer name to no avail.

The key tables and forms are

Report: CustomerHazWasteReport (This is one where I'm trying to inset the total for each specific customer)
Table: Incoming Waste (where the report body content is held)
Table: Clients & Customers (obvious - test data)
Form: IncomingWaste2 (Where the data is entered into the Incoming Waste Table and shows the layout of the data. All the data presented in here from the Incoming Waste table (proper data).
Query: QTYTOTALS (This is the query that I used to summer the individual consignments.)

Hopefully this all makes sense - (even if it is rather cumbersome and amatuerish.) With the exception of these Total calculations, this database works well for the given purpose and is in continuous development.

When you view the CustomerHazWasteReport you'll see that components are listed according to the manner in which they were entered in the Form. By Customer and then data

I have only managed to get <#error> in the totals field in each.

There's also aloose end on the Form (in the blue box near the top right) I need to put a 'running total' in here when a customer record is selected.... but I'll get to that after I understand where I'm going wrong with the DLookUp function.

Can you assist on the Report ? Look forward to your response.

Best Regards

=Jimbo=

Not getting this. The Text box on the report called Customer Name displays the customer name which it picks up from the underlying table. I have no other fields or text boxes called Customer Name, so I'm not sure what you are suggesting I change.

I'll have another play around with the Dlookup tomorrow morning. It's 11pm here now,and I have an grumpy wife...

Perhaps we can pick up tomorrow (assuming this isn't becoming too tedius)

I was serious about the single malt too

Best Regards and Thanks

=Jimbo=

[pbaldy]

I'm going to have to fit this in during the day. I think I know what the problem is, but I have to track it through. I think your lookup fields are the problem. Where you see a customer name in your query is actually a customer ID. If that ID is in the report, we'd want to use that. Most of us recommend against lookup fields, as they are PITA:

http://access.mvps.org/access/lookupfields.htm

[pbaldy]

Man, you worked me hard here. You also had a caption in the query; you have to use the actual field name. Try

=DLookUp("[expr1]","[QTYTOTALS]","[Customer] = " & [customer])

[Jamescdawson]

Man, you worked me hard here. You also had a caption in the query; you have to use the actual field name. Try

=DLookUp("[expr1]","[QTYTOTALS]","[Customer] = " & [customer])

You ARE the Man !!

It works a Treat !!

[pbaldy]

Happy to help. From your PM:

.....and if it isn't asking too much - can you explain the syntax. I don't understand the reasoning in the criteria element of the expression. (Why no " around the last element ??

First thing to keep in mind is that text values need to be surrounded by quotes, numeric values do not (that's the short version). That means your goal for a text value is:

Customer = 'ABC'

but for a numeric value

Customer = 123

So when we thought it was a text value, we concatenated the "'" to the end to add the ' to the end of the value (the one at the beginning was inside the quotes there). For a numeric value, we don't want the ' at the end, so the whole bit is dropped. Some people would do this:

=DLookUp("[expr1]","[QTYTOTALS]","[Customer] = " & [customer] & "")

But the last bit does nothing and can be dropped.