Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    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?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98

    Database Uploaded

    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=

    Quote Originally Posted by Jamescdawson View Post
    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=
    Attached Files Attached Files

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    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])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #20
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98

    Smile Yep !! That's it

    Quote Originally Posted by pbaldy View Post
    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 !!

  6. #21
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-09-2012, 05:27 PM
  2. Totals Query for Report
    By SpdRacerX in forum Queries
    Replies: 3
    Last Post: 05-01-2012, 02:25 PM
  3. Replies: 5
    Last Post: 12-06-2011, 11:18 AM
  4. Replies: 42
    Last Post: 07-13-2010, 02:49 PM
  5. Calculating Sub Totals in a Query
    By cassidym in forum Queries
    Replies: 2
    Last Post: 06-09-2010, 01:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums