Results 1 to 8 of 8

Thread: Expression Help

  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Expression Help

    Trying to figure out how to create a control source expression that adds the sum of a textbox [Text200] If another textbox [Text300] equals 8.

    =Sum(Abs[Text200])) WHERE [Text300]=8...something along these lines. I don't deal with the expression builder much except for small whimsical calculations on the fly for reports.

    Thanks!

    Version is 2010
    Last edited by Pis7ftw; 01-12-13 at 03:52.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    create a control source expression that adds the sum of a textbox [Text200]
    adds it to what? Also, how do you have a sum in a textbox? A textbox only contains a single value. Also, your controls' names are anything but intuitive. Always give the controls names that you, or the poor guy who has to maintain your program, can figure out instantly what they mean; eg txtFName for someone's first name.

    Sam

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    The textboxes in my report already hold values based on other calculations. So text200 holds the value of 2 and text300 holds the value of 3 therefore text400, which would contain the expression, will hold the value of 5.

    I do typically name my objects in everything with the exception of my whimsical calculations. These get created and deleted as needed so I never felt the need to name them. Also, it's a personal use program which isn't large in nature and nobody else would have a use for it.

    I'll try to clarify my original question.

    Text200 = 4
    Text300 = 5
    Text400 = 19

    I want to add Text200 to Text300 only if Text400 equals 19 and have the output displayed in TextAnswer.

    Thanks for you reply!
    Version: Access 2010

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You need to understand that TextAnswer is a Calculated Field, and as such should only be used for display in a Form or Report, it should not be saved to the Table, so it should be Unbound. Anytime you need the value, again, you simply recalculate it.

    So in the ControlSource Property, for TextAnswer, use this

    Code:
    =IIf([Text400]=19,[Text200]+[Text300],"")


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2012
    Posts
    79
    That's what I'm looking for! Thanks!
    Version: Access 2010

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Nov 2012
    Posts
    79
    Quote Originally Posted by Pis7ftw View Post
    The textboxes in my report already hold values based on other calculations. So text200 holds the value of 2 and text300 holds the value of 3 therefore text400, which would contain the expression, will hold the value of 5.

    I do typically name my objects in everything with the exception of my whimsical calculations. These get created and deleted as needed so I never felt the need to name them. Also, it's a personal use program which isn't large in nature and nobody else would have a use for it.

    I'll try to clarify my original question.

    Text200 = 4
    Text300 = 5
    Text400 = 19

    I want to add Text200 to Text300 only if Text400 equals 19 and have the output displayed in TextAnswer.

    Thanks for you reply!
    Ok, the solution you gave me turns out is closer than where I was but not quite where I need to be; not as specific as I need it to be. But I think we are on the right track.

    The above post still applies but with the added parameter that Text400 can be any integer between 1 and 23. Essentially what it turns out I really need is an expression that adds Text200 and Text300 only WHERE Text400 = 19. Then say Text100 and Text500 only WHERE Text400 = 13. In recap:

    Text100 = 2
    Text500 = 3
    So WHERE Text400 = 13 then the unbound textbox would = 5. Otherwise this expression wouldn't even be considered.

    But

    Text200 = 4
    Text300 = 5
    WHERE Text400 = 19 then the unbound textbox would = 9. Otherwise this epression wouldn't even be considered.

    Another way I can put it is I only want certain things calculated if Text400 = a certain number and I want things calculated a different way if Text400 = a different number.

    Is this possible in an expression or would I be better off using VBA for this?

    Again thanks for your assistance!
    Version: Access 2010

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it were me I'd use an expression IN VBA
    is text400 a column in an underlyign taBLE

    If not then it needs to be set in the forms on current event (whichis fired when a row changes

    I'd create a sub routine
    Code:
    private sub CalcSomething() 
      if TEXT400 = 19 then
        textanswer.value = 0 'our defualt value is 0
        'only add t200 & 300 if they actually are numeric
        if isnumeric(text200.value) then textanswer = text200.value
        if isnumeric(text300.value) then textanswer = textanswer + text300.value
      else
        'else whatver other calculation you need
      endiuf
    end sub
    then call that subroutine where required
    eg the forms on current event, whn text200,300 or 400 change

    BTW I think you would benefit from giving more meaningful names to your controls, instead of using the defaults supplied by the Access form designer
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •