Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95

    How to total on a field column on a form

    I figured out how to do a total calculation on my invoice form. However, I didn't think that thru properly because there actually two separate fields that have to totals for. In the [InvoiceFrm], there are two calculations that need to be preformed. One is for the invoices that have been processed and another is for the unprocessed amount. (I can do it easy enough with a query, which I did called ProcessedQry, however if there a way to add a criteria on the form for both calculations to work.

    On the parent form in the ProcessedBalance, this should total for only the invoices that have something in the SES field. If the field is Null, then is should fill into the Non-Processed Balance field which is also on the parent form.

    Did that make any sense, if not I'll keep looking. I can't seem to ask the right question when I'm searching the web. Should I be looking at inputting the query totals into this form?


    Any help would be greatly appreciated. I attached my db again, just please keep in mind that I'm a complete beginner, lol
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good grief! Is everybody using a huge screen except for me? Seems that nothing fits when I download. A 19" form doesn't really fit on a 15" laptop - at least not at any resolution that I would be able to read! Takes 15 or so minutes just to squeeze everything into something that I can see.
    this should total for only the invoices that have something in the SES field
    Then shouldn't the calculation be 9000, not 7000?
    Not too clear to me what you're after.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    The sum is correct as it is. For each PO, there is also a PO Line, which works correctly. However, the calculation is incorrect, like if I was to add in a new invoice that does not yet have a SES so that field would be blank The sum changes and it should until some data is entered for the SES. Does that help out at all?

    (Sorry about the screen! Please ignore my comment on the query, I still don't have that working correct either, but I'm sure I will fix that, if that is the issue, meaning I should be using a query and inserting it's value into the form.)

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just a couple of quick observations then will be getting back to the ball game and my Scotch for tonight!
    You're storing PO in PO line - should not. Should be PO ID. PO Line seems redundant at the moment, because you have PO Line ID.
    My other comment based on this
    this should total for only the invoices that have something in the SES field
    The total based on that premise is 9000
    Invoice InvAmt SES
    t1 5000 1000125456
    t2 2000 1000124987
    t22 2000 100444586

    Will probably have to drop this for 2night.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    No problem, that for your thoughts on the issue.

    I'm not looking to fix the issues with my naming convention of the table fields, because all that works correctly. The single form you'll see when you launch the form, is for one PO and one PO line, which shows at the top of the form. When you select next, is when you see the additional $2K. That single form only has $7k, which totals correctly except for the exception of needing to not calculate the sum if the SES is blank/null.

    (Thanks for the help, and have a drink for me.)

  6. #6
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    I cleaned up the db a little with the [ProcessedQry]. This query displays the correct totals for only the processed invoices, meaning that I would still need to join with another query for the [NonProcessedInvoices] if that is they way to make it work and utilize in my db.

    I started by asking about the [InvoiceFrm] and saying that I figured out how to display the totals for the invoices, but then I realized that's not actually what I need. I need to show the totals for those invoices but only for the invoices where there is no [SES] or [SESDate]. That where I tried to start altering the query listed above, however I would rather see if there is a way to do it thru the form. The issue is that I just need to calculate or sum the invoices if there is no [SESDate] for the processed invoice field, and calculate or sum if there is something is the date field and it is not null.

    I spent hours reading articles I found online, but everything I find just showed me how to do the calculation. I found nothing about adding in a condition like I could possibly do with a query.

    Any help would be greatly appreciated.
    Attached Files Attached Files

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not looking to fix the issues with my naming convention of the table fields,
    That comment has nothing to do with names, but the way you're storing related data. Spent more time moving stuff on your form after reading "when you select next" because I couldn't see any way to do that. Eventually turned on scroll bar so I could see your nav buttons. They don't work so I can't "select next". Maybe we can shorten this if I say what you probably need in the calculated controls is DSum with criteria but your descriptions of the problem are too vague. You need to describe like you're talking to a 4 year old - because we know nothing about how to navigate such forms in order to replicate an issue. When it comes to understanding why these values need to be considered but those don't, it requires a description suitable for someone who knows nothing of the business. It's especially important to be able to do things that you describe, such as "select next". Look up DSum with criteria and see if that gets you on the right path. I'm thinking your criteria logic might be "WHERE SES Is Null" or similar.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    Thanks, I'll look over the DSUM option.

  9. #9
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    I'm working on the DSUM and I've looked at at least 30 different searches to try to figure this out. This is what I have so far, but I can not figure out how to put in the IsNotNull criteria for the [SES] field.

    Code:
    ProcessedBalance: DSum("[InvAmt]","InvoiceSubform",IsNotNull("[SES]")
    Can someone show me what I'm doing wrong or point me to a website that will explain this criteria? Thanks

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    What value are you expecting to see in the query result?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    Well, hopefully I will see $7k in the expression result. However, as of right now it's telling me that I have invalid syntax.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    This is the query without any criteria set.

    I cannot see how we can get to a value of $7k - which values would you want filtered out?
    Attached Thumbnails Attached Thumbnails qry.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    Because for the PO 4500100001 and POLine 10 totals $7K for when the SES field is empty.

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Delta729 View Post
    Because the PO 4500100001 and POLine 10 totals $7K for when the SES field is empty.
    I need to go to specsavers , I can only see 8000?

    Edit: I think I see what you want, but you did not explain it well at all?
    You want the total of the same po and po line for any po and po line that has an empty SES?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    The 2nd and 3rd lines should be what is added. The top line is blank or empty for the SES. The 4th line is for a different [POLine].
    2nd line is $2K
    3rd line is $5K

    That $7K or did I forget how to add when I had my stroke?

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

Similar Threads

  1. Exclude Column from Total Sum
    By ratcat in forum Access
    Replies: 4
    Last Post: 03-18-2019, 05:46 PM
  2. Total Column Not Summing Fields
    By janmack79 in forum Queries
    Replies: 2
    Last Post: 02-12-2019, 03:46 PM
  3. Formatting the total column of a crosstab
    By keith2511 in forum Queries
    Replies: 4
    Last Post: 11-23-2018, 03:35 PM
  4. Total Column in Query
    By spoolinaz in forum Access
    Replies: 10
    Last Post: 01-03-2014, 12:26 PM
  5. Total from subform column?
    By CyberRaptor in forum Forms
    Replies: 2
    Last Post: 01-17-2013, 05:25 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