Results 1 to 4 of 4
  1. #1
    knh2r is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    25

    Sub total and total on invoice form

    Hey guys. The background to my issue is found under the thread "dimension dependent price on invoice".



    Here is the issue I have now. The individual formulas all work fine to at finding the price of each line item. They look up the "girth factor" and everything. I just can not for the life of me figure out how to make the subtotal, tax, and total fields all work in the subform footer. I have attached a picture of my subform in design view hoping that helps you see my issue.

    Also the formulas in each field are:

    UnitPrice =(((2*[W])+(2*[H]))*(DMin("GirthValue","tblGirths","GirthType=" & "fkProductID" & " And GirthRange>=" & IIf([L]=0,1,[L]))))

    LineTotal=([UnitPrice]*[Qty])

    I have also tried bringing the "girth factor" found using the Dmin portion of the formula above into the form separately.

    The forumulas I have in the footer are:

    Subtotal=Sum([LineTotal])

    Tax=((0.07)*[Subtotal])

    Order Total=([Tax]+[Subtotal])

    All I get is a balnk field. My understanding is that this is because I am trying to Sum a calculated field. I just don't know what to do instead.

    I have tried recalculating in each field and this works perfectly until I go to the next record in the subform (add another item to the invoice). Then the fields display only the amounts for that single record.(Obviously).

    I am a newbie so I'm sure I'm missing something.

    Thanks for all your help in advance!
    Click image for larger version. 

Name:	Subform Design view.PNG 
Views:	12 
Size:	57.5 KB 
ID:	25153

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont put quotes around the field value: "GirthType=" & fkProductID & " And GirthRange>=" & IIf([L]=0,1,[L]))))

  3. #3
    knh2r is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    25
    In the unitprice field? When I do that the unitprice fails to work. Right now only the subtotal, tax, and order total fields don't work.

  4. #4
    knh2r is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    25
    So it should look like this?

    =(((2*[W])+(2*[H]))*(DMin("GirthValue","tblGirths","GirthType=" & fkProductID & " And GirthRange>=" & Nz([L],1))))

    When I do that it does continue to work on each line item, but it automatically inserts [] around fkProductID anyway...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  2. Replies: 4
    Last Post: 10-10-2014, 02:39 PM
  3. How to calcutate sub total in form?
    By dododo in forum Forms
    Replies: 3
    Last Post: 05-17-2013, 09:41 PM
  4. Display Total on a form
    By Ray67 in forum Forms
    Replies: 21
    Last Post: 06-18-2012, 03:49 PM
  5. Total Box on Form
    By Bike in forum Forms
    Replies: 3
    Last Post: 04-05-2011, 01:30 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