Summing numbers in a ListBox

[Gator]

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)

 

[Pat Hartman]

No. You would create a separate query and use DSum() to sum the data.

 

[Douglas J. Steele]

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])

 

[Gator]

works good except my sum seems to be rounding up. the amount is .47 and the
text box is showing .50
--
Gator

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

(no e-mails, please!)

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)

 

[Douglas J. Steele]

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

(no e-mails, please!)

works good except my sum seems to be rounding up. the amount is .47 and
the
text box is showing .50
--
Gator

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

(no e-mails, please!)

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]

I formatted the Table field as Currency & Auto Decimal.
--
Gator

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

(no e-mails, please!)

works good except my sum seems to be rounding up. the amount is .47 and
the
text box is showing .50
--
Gator

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

(no e-mails, please!)


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)

 

[Douglas J. Steele]

But what did you use in the function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I formatted the Table field as Currency & Auto Decimal.
--
Gator

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

(no e-mails, please!)

works good except my sum seems to be rounding up. the amount is .47
and
the
text box is showing .50
--
Gator


:

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

(no e-mails, please!)


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]

I used Long...but I changed it to Currency and it works...many many thanks
--
Gator

But what did you use in the function?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I formatted the Table field as Currency & Auto Decimal.
--
Gator

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

(no e-mails, please!)


works good except my sum seems to be rounding up. the amount is .47
and
the
text box is showing .50
--
Gator


:

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

(no e-mails, please!)


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)