Display the Sum of a Query Result in Decimals

[RunTime91]

Hello Again...



The query populates a ListBox from which I loop through to sum the Hours Worked column...

The issue I am having is that the TextBox won't display decimals.

I have changed as many setting as I can and still nothing?

could the source have something to do with this...

Thanks for your help...

[June7]

First you say you are populating a ListBox then you say TextBox won't display decimals. What has listbox got to do with the textbox? What source - the listbox RowSource?

[John_G]

The issue I am having is that the TextBox won't display decimals.

If the text box is bound to a table field of type integer, then it won't display decimals. If the textbox is unbound, or is bound to a field that is numeric but not integer, then you need to set the Format property to display a fixed number of decimals.

[RunTime91]

Hey June ~

The ListBox displays the results of the query of which there is a column indicating the hours each employees enters at the end of each day. Thus an employee may enter 8, 8 7.5, 6.75, 8.5 etc, etc.

Once the ListBox is populated with the query results I loop through the values in the 'HoursWorked' column to sum the total hours and I want to display that result in a TextBox. If my code were to add the results

of the hours I posted in the previous sentence it would display in the TextBox 38 instead of 38.75

The source in this instance is the table data.

[aytee111]

First, the field on the table must be Double, or at least no kind of integer. Second, are the decimals being displayed in the query for the listbox correctly? If so, then the format of the textbox should be such that decimals are displayed.

[RunTime91]

Hello Aytee ~

Yes, the ListBox does display the decimals correctly, so I will check the field and TextBox properties/formatting when I get to work in about an hour...Thanks as always for your help

[RunTime91]

Update...

Apparently, the reason the TextBox is not showing the decimals is because the Sum loop is not recognizing them. When I step through the Sum loop and stop on the sum variable after each pass the intelisense is showing the whole number and not decimals after it.

Thus, if an employee enters 9.75 and the ListBox shows 9.75 when it is summed the intelisense shows 9

Here is the Sum Loop

Dim LstRows As Integer
Dim LstSum As Integer
LstRows = Me![LstTempTimeView].ListCount
X = 1
Do
LstSum = LstSum + (Me![LstTempTimeView].Column(6, X))
X = X + 1
Loop Until X = LstRows
Me![Text4] = LstSum

Can anyone see a reason why this loop wont recognize the decimals?

When I place (Me![LstTempTimeView].Column(6, X)) into the immediate window it will return the correct result

Thanks as always...

[June7]

LstSum is declared as an Integer. Integer cannot have decimal. Declare LstSum as Double.

[RunTime91]

June ~ You Rock!!!! Simple as that!!

Thank you very much...

Quick question... Is looping through the rows in the ListBox the best way to find the sum... (it is the only way I know in my Access infancy)

Thanks Again...

[June7]

What is the RowSource of the listbox? Could probably use the same filter criteria in an expression using DSum() domain aggregate function. However, domain aggregate functions can perform slowly in query and in textbox.

[RunTime91]

Hey June ~

The RowSource is set at RunTime ~ Code is below

Me.LstTempTimeView.RowSource = "SELECT tbl_AssocInfo.DB_ID, tbl_AssocInfo.Assoc_ID, [tbl_AssocInfo].[First_Nm] & "" "" & [tbl_AssocInfo].[Last_Nm] AS Name, tblTeamAssignment.TeamID, tblContractorInfo.TempAgency, tblDailyHoursWorked.Entered, tblDailyHoursWorked.HoursWorked, tblTeamAssignment.TeamEnd " & _
"FROM ((tblDailyHoursWorked INNER JOIN tbl_AssocInfo ON tblDailyHoursWorked.DB_ID = tbl_AssocInfo.DB_ID) INNER JOIN tblContractorInfo ON tbl_AssocInfo.DB_ID = tblContractorInfo.DB_ID) INNER JOIN tblTeamAssignment ON tbl_AssocInfo.DB_ID = tblTeamAssignment.DB_ID " & _
"WHERE (((tbl_AssocInfo.Assoc_ID) Like ""C*"") AND ((tblTeamAssignment.TeamID)=[Forms]![frmTempTimeTest]![CboTeamName]) AND ((tblDailyHoursWorked.Entered) Between [Forms]![frmTempTimeTest]![TxtBegDate] And [Forms]![frmTempTimeTest]![TxtEndDate]) AND ((tblTeamAssignment.TeamEnd) Is Null));"

[June7]

That's a lot of criteria but something like:

DSum("HoursWorked", "query name here", "Assoc_ID Like 'C*' AND TeamID=[Forms]![frmTempTimeTest]![CboTeamName] AND Entered Between [Forms]![frmTempTimeTest]![TxtBegDate] And [Forms]![frmTempTimeTest]![TxtEndDate] AND TeamEnd Is Null")

or concatenate variable references:

DSum("HoursWorked", "query name here", "Assoc_ID Like 'C*' AND TeamID=" & [Forms]![frmTempTimeTest]![CboTeamName] & " AND Entered Between #" & [Forms]![frmTempTimeTest]![TxtBegDate] & "# And #" & [Forms]![frmTempTimeTest]![TxtEndDate] & "# AND TeamEnd Is Null")