Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: sum values not based in a table

    Is it possible to sum values that are calculated on a form that is associated with a query.

    Example. User types in a number and the query spits out info in the text boxes. A few other text boxes not in the query are populated by a calculation. I need to sum the text boxes that are not associated with the query. I tried these functions. =([Textboxname]+[Textboxname]) but all the text box shows are the values for those text boxes and not the actual calculation. I also tried =sum([textboxname]+[textboxname]) but no value is returned. The only other kicker is I don't know how to code so I am probably limited to function at the form level or query level. Thank you very much.

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I think I am doing what you are doing and it works for me.

    One of my tabular forms has a calulated text box =Sum([Fieldname]) adding each of four columns and then I have for the total of those four colums

    =([Text66]+[Text44]+[Text45]+[Text48])

    And all is OK

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Sometimes when I calculate answers and then have another control calculating an answer based on previous calculations I run into trouble.

    Your example gives the general idea, "=([Textboxname]+[Textboxname]) ", of what you are doing. To dig further, do you want to post some sample controls (both control name and datasource), and then the final calculation (both control name and datasource)?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    This is the function that is in the text box right now.

    =([2001 Miles]+[1999 Miles]+[2000 Miles]). That is in the control source.

    [2001 Miles] ][1999 Miles][2000 Miles] are calculations that are associated with the query.

    After the query is complete 2001 Miles = 0, 2000 Miles = .3 and 1999 Miles = 0.

    The Text box shows the 0.30. So, its showing the values but not the calculation.

    Maybe this will help.

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    After the query is complete 2001 Miles = 0, 2000 Miles = .3 and 1999 Miles = 0.

    The Text box shows the 0.30. So, its showing the values but not the calculation.


    But .3 is your answer!! at least if text box is adding 2001 Miles, 1999 Miles and 2000 Miles

  6. #6
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    What are the control names? Though you didn't post the names of the controls, lets see if this helps.

    control name = txt1999Miles
    control source = [1999 Miles]

    control name = txt2000Miles
    control source = [2000 Miles]

    control name = txt2001Miles
    control source = [2001 Miles]

    control name = txtTotalMiles
    control source = [txt1999Miles]+[txt2000Miles] +[txt2001Miles]
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  7. #7
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Good point Mike
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  8. #8
    Join Date
    Mar 2004
    Posts
    361
    the answer of 0.30 is actually just the values of the text boxes. I added 5 other text boxes and I get 0.300000.49. So its just showing the values.

    The control name is 2001 miles, 2000 Miles and 1999 Miles.
    The Control source is the same as above. These are in the property level of the text box.

    =([2001 Miles]+[1999 Miles]+[2000 Miles] that is the control source for the calculation text box. The name of the text box is text54

    I hope that helps

    Is there another property I should change in order for the function to calculate?

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    jpshay

    The other day I was pulling my hair out thinking I had a problem. I had made up in a query a calculated field to do stamp duty on insurance premiums which was at 5%. Now whether premium is paid monthly or yearly or half yearly the stamp duty is 5% of the installement.

    So I open my form and entered $100 and selected "paid monthly" and stamp duty was at $5 so all was well. I then selected Yearly from a drop down box and stamp duty stayed at $5. Same deal for Quarterly and Half Yearly.

    It must have taken me about half an hour to catch on that the $100 was not changed and by selecting Yeary instead of monthy I was effectively reducing the total annual premium from $1200 to $100. And of course a $100 per month or a $100 per year still resulted in a $5 stamp duty per installement.

    Sometimes this stuff can make you giddy.

    Mike

  10. #10
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    good programming techniques: 1) change the names of the controls so they are different than the control source. 2) Use prefixes in the control names so that they are distinguishable in code.

    I suggest that you change the control names as suggested a couple of posts above and see what happens. Hope it helps.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  11. #11
    Join Date
    Mar 2004
    Posts
    361
    I changed the names to 1999_Miles , 2000_Miles and 2001_Miles. Unfortunatly that didn't help. I am still getting the values that are in the text boxes and not the actuall calculation. ARGGGG. I hate access sometimes.

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Can you change your data so that after the query is complete

    2001 Miles = 1 or more
    2000 Miles = .3
    1999 Miles = 1 or more

  13. #13
    Join Date
    Mar 2004
    Posts
    361
    I just tried that and I get the same thing. 003 .

    This is the calculation in the query that is associated with 2000 Miles

    2000_Miles: IIf([mileage] Between "1" And "16000" And [Year]="2000","3",IIf([mileage] Between "16001" And "24000" And [Year]="2000",".2",IIf([mileage] Between "24001" And "32000" And [Year]="2000",".1",IIf([mileage] Between "32001" And "40000" And [Year]="2000","0",IIf([mileage] Between "40001" And "48000" And [Year]="2000","-.1",IIf([mileage] Between "48001" And "56000" And [Year]="2000","-.2",IIf([mileage]>"56000" And [Year]="2000",".-3","0")))))))

    Maybe that will help.

  14. #14
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Two thoughts. First, access uses underscores to translate some characters. Though tedious, I recommend changing the names as mentioned above. This will erase any doubt about what is being added.

    control name = txt1999Miles
    control source = [1999 Miles]

    control name = txt2000Miles
    control source = [2000 Miles]

    control name = txt2001Miles
    control source = [2001 Miles]

    control name = txtTotalMiles
    control source = [txt1999Miles]+[txt2000Miles] +[txt2001Miles]

    Second. Are the results you are seeing concatentations? Is "0.300000.49" the concatentation of 0.3 +0 + 0+ 0+0 + 0.49? See if the reuslts are still the same after you try the above. If so, do any of the values get changed to character type instead of numbers somewhere along the line?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  15. #15
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Just looked at your query.
    1) You have the value "3" instead of ".3"
    2) You have a value of ".-3" instead of "-.3"
    3) most importantly, I would get rid of the quotation marks and see if that is what is translating your numbers to characters.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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