Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Unanswered: Adding the sum of several txt boxes

    I am using access 2003. I am trying to add the sum of several txt boxes on a report. I can subtract, multiply and divide but am unable to add.

    For example say that text22 has a value of 22, and text24 has a value of 24. I created an additional text box in the page footer and added =[text22]+[text24], the sum would come out as 22 24. In the same instance if I subtracted =[text22]-[text24] then the answer would be -2

    Why will everything but adding work. Am I missing a reference?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The plus sign is a good one eh, looks liks your report is concatenating the values, is that right?

    The reason it does this is because it sees text values, so when using the plus symbol, [historically] a valid concatenator, it does what it should be doing!

    When subtracting, however, you can't subtract two strings, so Access does something clever and converts the strings into numeric values so that it cna perform this calculation.

    Hopefully this will explain the problem well enough for you to come up with a solution on your own; however if you get stuck post what you've tried and I'm sure someone will gladly help.

    P.S. if you do work it out on your own - post the resolution so that future readers can benefit too

    Cheer,
    George
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65
    Hey George thanks for the reply, about 5 minutes after I posted I got a handle on it. What I am having troubles with now is that I can only put whole numbers in the box. When I put say 2.5 in the the number either rounds up or down.

    I am not that good with VBA so writing some code to handle this is beyond me.

    Mike

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So what was the problem and how did you fix it? Future readers might benefit from some details.

    Your new problem is probably the result of entering the value into a field that is set to Integer instead of Single or Double.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wonder if there are any functions which convert datatypes..

    memo to self MUST look in the helpfile
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Quote Originally Posted by healdem
    wonder if there are any functions which convert datatypes..

    memo to self MUST look in the helpfile
    Hi not sure about the VB side of it, but if you use this this function Val([text22]) + Val([Text24]) within a query, the Val function converts the txt value, into a numeric value, this way the Concatenation effect does not take place, plus you can also format it double etc... format(Val([Text22]),00)
    Last edited by MarkWhyte; 05-22-08 at 08:47.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Val is a very icky function, there are better alternatives; check the helpfiles for more information
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    LOL @ all the hints for people to use the help files!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Quote Originally Posted by StarTrekker
    LOL @ all the hints for people to use the help files!
    Where are the said help files ?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When in MSAccess, have you ever hit the F1 key?
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or used the Help menu?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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