Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37

    How to sum a textbox based on two other values


    I have three textbox and two of them are text and the third add up what was entered based on the 2 other text. I'm trying to get the third text to sum the values entered for site name and station name


  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Welcome to the site. Your image didn't get attached properly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37
    Here is the code in expression builder =IIf(Val([Site Name].[Text]) And Val([Station Name].[Text])LIKE([Data Collection] Sum([Data Collection].[Liquid Amount Gallons]))

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    LIKE without wildcard should probably just be = sign.

    You probably want to use & for concatenation, not AND. Don't use Text property. Applying VAL function to alpha data makes no sense. The expression as a whole makes no sense. Probably want syntax like:

    =Sum(IIf(some expression here, [Liquid Amount Gallons], Null))

    Show some sample data and desired result.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try without the .Text property. You can only use that if the control has focus. Plus you seem to be missing commas. The structure of an IIf() is:

    IIf(Test resulting in true or false, what to do if true, what to do if false)

    You don't appear to have a valid test, nor the true and false arguments. In words, how would you complete the formula using the structure above?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do yourself a favor and DO NOT use spaces in object names.
    Object names include Field names, Table names, Query names, Form names and Report names.

    Examples
    Current
    Better (uses underscore)
    Best (IMHO)
    [Site Name] [Site_Name] [SiteName]
    [Station Name] [Station_Name] [StationName]
    [Data Collection] [Data_Collection] [DataCollection]
    [Liquid Amount Gallons] [Liquid Amount_Gallons] [LiquidAmountGallons]

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Let's try to analyze your formula.
    =IIF(Condition, ExpressionWhenTrue, ExpressionWhenFalse)

    1. Obviously, you missed a delimiter before SUM(), which logicallly must be ExpressionWhenTrue;
    2. ExpressionWhenFalse is missing, so in case Condition is FALSE, FALSE is returned;
    3. ExpressionWhenTrue returns SUM of field [Liquid Amount Gallons] of all rows in table [Data Collection]. I.e. there must be such table!;
    4. Condition is a logical expression like (Control1 AND Control2 Like Control3), where Control1 =
    [Site Name], Control2 = [Station Name], and Control3 = [Data Collection];
    5. The expression Control1 AND Control 2 works only, when both controls are linked to TRUE/FALSE fields (in this case it is a regular logical expression), or numeric fields (in this case the expression returns TRUE when both values are equal, and FALSE otherwise). When those fields are text, an error is returned;
    6. The expression (Control1 AND Control2) LIKE Control3 assumes, that the control
    [Data Collection] is also linked to either TRUE/FALSE or numeric field;
    7. When 5. and 6. aren't case, and controls
    [Site Name], [Station Name], and [Data Collection] are text values, the current formula can't work. And another problem is at hand:
    a) This indicates, that you don't have really a single table
    [Data Collection] present - probably you don't have such table at all. Instead you have a lot of tables with similar structure - a table for every value in field linked to control [Data Collection]. When this is the case, this is a FAT NO!

    Last edited by ArviLaanemets; 05-18-2022 at 05:21 AM.

  8. #8
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37

    Thumbs up

    Quote Originally Posted by June7 View Post
    LIKE without wildcard should probably just be = sign.

    You probably want to use & for concatenation, not AND. Don't use Text property. Applying VAL function to alpha data makes no sense. The expression as a whole makes no sense. Probably want syntax like:

    =Sum(IIf(some expression here, [Liquid Amount Gallons], Null))

    Show some sample data and desired result.
    That got me 90 percent there, but I want the liquid value based on the site name and station name. If I type in test1 and storage it should be 45, but the liquid amount is adding all data see the attached

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Sonyat810 View Post
    ....... see the attached
    You don't appear to have attached anything
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37
    Can you see it now

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Sonyat810 View Post
    Can you see it now
    NO :-(
    You cannot just paste pictures here, although the site makes it look like you can, you need to attach the file.

    See 'How to attach files' in the toolbar.
    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

  12. #12
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37
    Look at the form that says Report center. Every site is suppose to have a different name and different kinds of Station names. Instead the Liquid Amount is giving me the total of everything and also, when I try to clear the filter the liquid amount values stays. I get an error in VBA saying I can't attach an object to that field.
    Attached Files Attached Files

  13. #13
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37
    Did you have the chance to look at it

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why isn't control for Site Name also a combobox?

    Why is RecordSource a GROUP BY query when there is no aggregate calc?

    Since purpose of form is to select filter criteria why even bother with showing gallons?

    Since this form is not displaying data, why even bind it to table/query?

    This Sum expression is useless mostly because the IIf depends on fields of form RecordSource and the record that has focus never changes.

    I cannot get Sum to work with IIf condition at all, however, a DSum will work. Rename controls to tbxSite and cbxStation.
    =DSum("[Liquid Amount Gallons]","[Data Collection]","[Site Name]='" & [tbxSite] & "' And [Station Name]='" & [cbxStation] & "'")

    There is no need to bind form to dataset.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    Sonyat810 is offline Advanced Beginner
    Windows 7 64bit Office 365
    Join Date
    May 2022
    Posts
    37

    Thumbs up

    Quote Originally Posted by June7 View Post
    Why isn't control for Site Name also a combobox?

    Why is RecordSource a GROUP BY query when there is no aggregate calc?

    Since purpose of form is to select filter criteria why even bother with showing gallons?

    Since this form is not displaying data, why even bind it to table/query?

    This Sum expression is useless mostly because the IIf depends on fields of form RecordSource and the record that has focus never changes.

    I cannot get Sum to work with IIf condition at all, however, a DSum will work. Rename controls to tbxSite and cbxStation.
    =DSum("[Liquid Amount Gallons]","[Data Collection]","[Site Name]='" & [tbxSite] & "' And [Station Name]='" & [cbxStation] & "'")

    There is no need to bind form to dataset.
    Worked like a charm. Thank you so much!

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

Similar Threads

  1. Replies: 4
    Last Post: 05-04-2022, 02:42 PM
  2. Replies: 13
    Last Post: 06-01-2020, 07:19 PM
  3. Replies: 5
    Last Post: 09-12-2017, 12:41 AM
  4. Replies: 5
    Last Post: 02-04-2017, 07:34 AM
  5. Filter and Sort a form based on TextBox Values.
    By Ramun_Flame in forum Programming
    Replies: 7
    Last Post: 10-28-2012, 06:53 AM

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