If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Summing numbers in a ListBox
Can I put the following formula in a text box on a form to add numbers listed
in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#2
|
|||
|
|||
Summing numbers in a ListBox
No. You would create a separate query and use DSum() to sum the data.
"Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#3
|
|||
|
|||
Summing numbers in a ListBox
No, for a couple of reasons.
First is that all [Forms].[Form1].[ListBox].column(1) gives you is the value in the second column of the selected row in the list box (assuming that it's not set for MultiSelect). Second is that values in list boxes are actually strings, even if they strictly contain digits. (Okay, this isn't necessarily an issue: Access will automatically do the conversion for you if things are set up properly). If what you're saying is that you want the sum of all of the values in the second column of a given listbox, try the following function: Function AddListbox(WhichListbox As Control) As Long Dim lngLoop As Long Dim lngSum As Long lngSum = 0 For lngLoop = 0 To (WhichListbox.ListCount - 1) lngSum = lngSum + CLng(WhichListbox.Column(1, lngLoop)) Next lngLoop AddListbox = lngSum End Function You can then set the ControlSource of the text box to =AddListbox(Forms].[Form1].[ListBox]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#4
|
|||
|
|||
Summing numbers in a ListBox
works good except my sum seems to be rounding up. the amount is .47 and the
text box is showing .50 -- Gator "Douglas J. Steele" wrote: No, for a couple of reasons. First is that all [Forms].[Form1].[ListBox].column(1) gives you is the value in the second column of the selected row in the list box (assuming that it's not set for MultiSelect). Second is that values in list boxes are actually strings, even if they strictly contain digits. (Okay, this isn't necessarily an issue: Access will automatically do the conversion for you if things are set up properly). If what you're saying is that you want the sum of all of the values in the second column of a given listbox, try the following function: Function AddListbox(WhichListbox As Control) As Long Dim lngLoop As Long Dim lngSum As Long lngSum = 0 For lngLoop = 0 To (WhichListbox.ListCount - 1) lngSum = lngSum + CLng(WhichListbox.Column(1, lngLoop)) Next lngLoop AddListbox = lngSum End Function You can then set the ControlSource of the text box to =AddListbox(Forms].[Form1].[ListBox]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#5
|
|||
|
|||
Summing numbers in a ListBox
If you're getting a decimal point, then obviously you changed my code, since
I assume Long Integers, which don't support decimal points. What data type did you choose instead of Long? If you're working with currency, you should use the Currency data type, and the CCur function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... works good except my sum seems to be rounding up. the amount is .47 and the text box is showing .50 -- Gator "Douglas J. Steele" wrote: No, for a couple of reasons. First is that all [Forms].[Form1].[ListBox].column(1) gives you is the value in the second column of the selected row in the list box (assuming that it's not set for MultiSelect). Second is that values in list boxes are actually strings, even if they strictly contain digits. (Okay, this isn't necessarily an issue: Access will automatically do the conversion for you if things are set up properly). If what you're saying is that you want the sum of all of the values in the second column of a given listbox, try the following function: Function AddListbox(WhichListbox As Control) As Long Dim lngLoop As Long Dim lngSum As Long lngSum = 0 For lngLoop = 0 To (WhichListbox.ListCount - 1) lngSum = lngSum + CLng(WhichListbox.Column(1, lngLoop)) Next lngLoop AddListbox = lngSum End Function You can then set the ControlSource of the text box to =AddListbox(Forms].[Form1].[ListBox]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#6
|
|||
|
|||
Summing numbers in a ListBox
I formatted the Table field as Currency & Auto Decimal.
-- Gator "Douglas J. Steele" wrote: If you're getting a decimal point, then obviously you changed my code, since I assume Long Integers, which don't support decimal points. What data type did you choose instead of Long? If you're working with currency, you should use the Currency data type, and the CCur function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... works good except my sum seems to be rounding up. the amount is .47 and the text box is showing .50 -- Gator "Douglas J. Steele" wrote: No, for a couple of reasons. First is that all [Forms].[Form1].[ListBox].column(1) gives you is the value in the second column of the selected row in the list box (assuming that it's not set for MultiSelect). Second is that values in list boxes are actually strings, even if they strictly contain digits. (Okay, this isn't necessarily an issue: Access will automatically do the conversion for you if things are set up properly). If what you're saying is that you want the sum of all of the values in the second column of a given listbox, try the following function: Function AddListbox(WhichListbox As Control) As Long Dim lngLoop As Long Dim lngSum As Long lngSum = 0 For lngLoop = 0 To (WhichListbox.ListCount - 1) lngSum = lngSum + CLng(WhichListbox.Column(1, lngLoop)) Next lngLoop AddListbox = lngSum End Function You can then set the ControlSource of the text box to =AddListbox(Forms].[Form1].[ListBox]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#7
|
|||
|
|||
Summing numbers in a ListBox
But what did you use in the function?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... I formatted the Table field as Currency & Auto Decimal. -- Gator "Douglas J. Steele" wrote: If you're getting a decimal point, then obviously you changed my code, since I assume Long Integers, which don't support decimal points. What data type did you choose instead of Long? If you're working with currency, you should use the Currency data type, and the CCur function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... works good except my sum seems to be rounding up. the amount is .47 and the text box is showing .50 -- Gator "Douglas J. Steele" wrote: No, for a couple of reasons. First is that all [Forms].[Form1].[ListBox].column(1) gives you is the value in the second column of the selected row in the list box (assuming that it's not set for MultiSelect). Second is that values in list boxes are actually strings, even if they strictly contain digits. (Okay, this isn't necessarily an issue: Access will automatically do the conversion for you if things are set up properly). If what you're saying is that you want the sum of all of the values in the second column of a given listbox, try the following function: Function AddListbox(WhichListbox As Control) As Long Dim lngLoop As Long Dim lngSum As Long lngSum = 0 For lngLoop = 0 To (WhichListbox.ListCount - 1) lngSum = lngSum + CLng(WhichListbox.Column(1, lngLoop)) Next lngLoop AddListbox = lngSum End Function You can then set the ControlSource of the text box to =AddListbox(Forms].[Form1].[ListBox]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
#8
|
|||
|
|||
Summing numbers in a ListBox
I used Long...but I changed it to Currency and it works...many many thanks
-- Gator "Douglas J. Steele" wrote: But what did you use in the function? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... I formatted the Table field as Currency & Auto Decimal. -- Gator "Douglas J. Steele" wrote: If you're getting a decimal point, then obviously you changed my code, since I assume Long Integers, which don't support decimal points. What data type did you choose instead of Long? If you're working with currency, you should use the Currency data type, and the CCur function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... works good except my sum seems to be rounding up. the amount is .47 and the text box is showing .50 -- Gator "Douglas J. Steele" wrote: No, for a couple of reasons. First is that all [Forms].[Form1].[ListBox].column(1) gives you is the value in the second column of the selected row in the list box (assuming that it's not set for MultiSelect). Second is that values in list boxes are actually strings, even if they strictly contain digits. (Okay, this isn't necessarily an issue: Access will automatically do the conversion for you if things are set up properly). If what you're saying is that you want the sum of all of the values in the second column of a given listbox, try the following function: Function AddListbox(WhichListbox As Control) As Long Dim lngLoop As Long Dim lngSum As Long lngSum = 0 For lngLoop = 0 To (WhichListbox.ListCount - 1) lngSum = lngSum + CLng(WhichListbox.Column(1, lngLoop)) Next lngLoop AddListbox = lngSum End Function You can then set the ControlSource of the text box to =AddListbox(Forms].[Form1].[ListBox]) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gator" wrote in message ... Can I put the following formula in a text box on a form to add numbers listed in a list box, column 2......=Sum[Forms].[Form1].[ListBox].column(1) -- Gator |
Thread Tools | |
Display Modes | |
|
|