Sum in Querydef

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms footer and try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
 
I assume that DB1 is the textbox name on the form? Sum (and other aggregate
functions) work only wth fields, not with controls, as its argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You must Sum
on the values of one or more fields that are in the form's Recordset, which,
from your code, would appear to be "rst.Fields(i)" field -- whatever its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)
 
The control source of the form is a crosstab query, and column headings part
of the crosstab query can vary. With this in mind, there is no way to do the
sum on the form. Is it possible to do the sum in the query? I already
populate all the controls on the form with the load event, if the sum was
included in the query I could do the same with it. Something like this.
sum(rst.Fields(i).Name)


Ken Snell (MVP) said:
I assume that DB1 is the textbox name on the form? Sum (and other aggregate
functions) work only wth fields, not with controls, as its argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You must Sum
on the values of one or more fields that are in the form's Recordset, which,
from your code, would appear to be "rst.Fields(i)" field -- whatever its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms footer and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
 
Try using a DSum function to calculate the sum of the desired field from the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when you do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ryan said:
The control source of the form is a crosstab query, and column headings
part
of the crosstab query can vary. With this in mind, there is no way to do
the
sum on the form. Is it possible to do the sum in the query? I already
populate all the controls on the form with the load event, if the sum was
included in the query I could do the same with it. Something like this.
sum(rst.Fields(i).Name)


Ken Snell (MVP) said:
I assume that DB1 is the textbox name on the form? Sum (and other
aggregate
functions) work only wth fields, not with controls, as its argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You must
Sum
on the values of one or more fields that are in the form's Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field -- whatever its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms footer and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
I couldnt get dsum to work. Is it not possible to do a sum in the case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it be
defined?
Also, why cant you just put a textbox in the header or footer of the form
and sum the textbox in the detail? I know its unbound but its showing values.
--
Please remember to mark this post as answered if this solves your problem.


Ken Snell (MVP) said:
Try using a DSum function to calculate the sum of the desired field from the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when you do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ryan said:
The control source of the form is a crosstab query, and column headings
part
of the crosstab query can vary. With this in mind, there is no way to do
the
sum on the form. Is it possible to do the sum in the query? I already
populate all the controls on the form with the load event, if the sum was
included in the query I could do the same with it. Something like this.
sum(rst.Fields(i).Name)


Ken Snell (MVP) said:
I assume that DB1 is the textbox name on the form? Sum (and other
aggregate
functions) work only wth fields, not with controls, as its argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You must
Sum
on the values of one or more fields that are in the form's Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field -- whatever its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms footer and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
No you cannot use the Sum function in VBA; it's only for query or for
control's ControlSource on form or report.

What error message or result did you get from the DSum function?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ryan said:
I couldnt get dsum to work. Is it not possible to do a sum in the case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it be
defined?
Also, why cant you just put a textbox in the header or footer of the form
and sum the textbox in the detail? I know its unbound but its showing
values.
--
Please remember to mark this post as answered if this solves your problem.


Ken Snell (MVP) said:
Try using a DSum function to calculate the sum of the desired field from
the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when you
do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ryan said:
The control source of the form is a crosstab query, and column headings
part
of the crosstab query can vary. With this in mind, there is no way to
do
the
sum on the form. Is it possible to do the sum in the query? I already
populate all the controls on the form with the load event, if the sum
was
included in the query I could do the same with it. Something like
this.
sum(rst.Fields(i).Name)


:

I assume that DB1 is the textbox name on the form? Sum (and other
aggregate
functions) work only wth fields, not with controls, as its argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You must
Sum
on the values of one or more fields that are in the form's Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field -- whatever
its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms footer
and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
It says #Name? I think the problem is that the field im wanting to sum is
the column heading field in the crosstab, which can produce 1-76 columns.
=Dsum([DB],[AllBalancesByFCandDB])
Here is the Crosstab Query

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Financial Class Total]
FROM AllCharges
WHERE (((InStr("," & [Forms]![Parameters].[LocationFilter] & ",","," & [db]
& ","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

On the form I have 76 unbound textboxes. The forms control sorce is set to
AllBalancesByFCandDB, and the onload event reads like this.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

Ken Snell (MVP) said:
No you cannot use the Sum function in VBA; it's only for query or for
control's ControlSource on form or report.

What error message or result did you get from the DSum function?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ryan said:
I couldnt get dsum to work. Is it not possible to do a sum in the case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it be
defined?
Also, why cant you just put a textbox in the header or footer of the form
and sum the textbox in the detail? I know its unbound but its showing
values.
--
Please remember to mark this post as answered if this solves your problem.


Ken Snell (MVP) said:
Try using a DSum function to calculate the sum of the desired field from
the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when you
do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The control source of the form is a crosstab query, and column headings
part
of the crosstab query can vary. With this in mind, there is no way to
do
the
sum on the form. Is it possible to do the sum in the query? I already
populate all the controls on the form with the load event, if the sum
was
included in the query I could do the same with it. Something like
this.
sum(rst.Fields(i).Name)


:

I assume that DB1 is the textbox name on the form? Sum (and other
aggregate
functions) work only wth fields, not with controls, as its argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You must
Sum
on the values of one or more fields that are in the form's Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field -- whatever
its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms footer
and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
The DSum expression should be this:

=DSum("DB","AllBalancesByFCandDB")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
It says #Name? I think the problem is that the field im wanting to sum is
the column heading field in the crosstab, which can produce 1-76 columns.
=Dsum([DB],[AllBalancesByFCandDB])
Here is the Crosstab Query

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Financial Class
Total]
FROM AllCharges
WHERE (((InStr("," & [Forms]![Parameters].[LocationFilter] & ",","," &
[db]
& ","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

On the form I have 76 unbound textboxes. The forms control sorce is set
to
AllBalancesByFCandDB, and the onload event reads like this.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

Ken Snell (MVP) said:
No you cannot use the Sum function in VBA; it's only for query or for
control's ControlSource on form or report.

What error message or result did you get from the DSum function?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ryan said:
I couldnt get dsum to work. Is it not possible to do a sum in the case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it be
defined?
Also, why cant you just put a textbox in the header or footer of the
form
and sum the textbox in the detail? I know its unbound but its showing
values.
--
Please remember to mark this post as answered if this solves your
problem.


:

Try using a DSum function to calculate the sum of the desired field
from
the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when
you
do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The control source of the form is a crosstab query, and column
headings
part
of the crosstab query can vary. With this in mind, there is no way
to
do
the
sum on the form. Is it possible to do the sum in the query? I
already
populate all the controls on the form with the load event, if the
sum
was
included in the query I could do the same with it. Something like
this.
sum(rst.Fields(i).Name)


:

I assume that DB1 is the textbox name on the form? Sum (and other
aggregate
functions) work only wth fields, not with controls, as its
argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You
must
Sum
on the values of one or more fields that are in the form's
Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field --
whatever
its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms
footer
and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like
this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
=DSum("DB","AllBalancesByFCandDB") didn't work either. I will go ahead and
mark this as answered since you have stuck with me for so long. If you come
up with any other possible solutions please let me know. It just doesn't
make sence that you can't just sum the unbound textbox, but oh well.


Ken Snell (MVP) said:
The DSum expression should be this:

=DSum("DB","AllBalancesByFCandDB")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
It says #Name? I think the problem is that the field im wanting to sum is
the column heading field in the crosstab, which can produce 1-76 columns.
=Dsum([DB],[AllBalancesByFCandDB])
Here is the Crosstab Query

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Financial Class
Total]
FROM AllCharges
WHERE (((InStr("," & [Forms]![Parameters].[LocationFilter] & ",","," &
[db]
& ","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

On the form I have 76 unbound textboxes. The forms control sorce is set
to
AllBalancesByFCandDB, and the onload event reads like this.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

Ken Snell (MVP) said:
No you cannot use the Sum function in VBA; it's only for query or for
control's ControlSource on form or report.

What error message or result did you get from the DSum function?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I couldnt get dsum to work. Is it not possible to do a sum in the case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it be
defined?
Also, why cant you just put a textbox in the header or footer of the
form
and sum the textbox in the detail? I know its unbound but its showing
values.
--
Please remember to mark this post as answered if this solves your
problem.


:

Try using a DSum function to calculate the sum of the desired field
from
the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when
you
do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The control source of the form is a crosstab query, and column
headings
part
of the crosstab query can vary. With this in mind, there is no way
to
do
the
sum on the form. Is it possible to do the sum in the query? I
already
populate all the controls on the form with the load event, if the
sum
was
included in the query I could do the same with it. Something like
this.
sum(rst.Fields(i).Name)


:

I assume that DB1 is the textbox name on the form? Sum (and other
aggregate
functions) work only wth fields, not with controls, as its
argument.
Therefore, if DB1 is a control, you cannot Sum on its values. You
must
Sum
on the values of one or more fields that are in the form's
Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field --
whatever
its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms
footer
and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query like
this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource = sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
OK, I think I'm understanding what you want. You want a sum of the values in
all the fields in all the columns --- the DB "column".

I believe there is no need for you to use the crosstab query directly for
your data. You can get the result directly from the AllCharges table/query,
using the same WHERE filter as in the crosstab query.

Try this for your unbound textbox's RowSoure expression:

=DSum("DB", "AllCharges", "InStr("",""" &
[Forms]![Parameters].[LocationFilter] & ""","","","" & [db] & "","")>0")
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
=DSum("DB","AllBalancesByFCandDB") didn't work either. I will go ahead
and
mark this as answered since you have stuck with me for so long. If you
come
up with any other possible solutions please let me know. It just doesn't
make sence that you can't just sum the unbound textbox, but oh well.


Ken Snell (MVP) said:
The DSum expression should be this:

=DSum("DB","AllBalancesByFCandDB")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
It says #Name? I think the problem is that the field im wanting to sum
is
the column heading field in the crosstab, which can produce 1-76
columns.
=Dsum([DB],[AllBalancesByFCandDB])
Here is the Crosstab Query

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Financial Class
Total]
FROM AllCharges
WHERE (((InStr("," & [Forms]![Parameters].[LocationFilter] & ",","," &
[db]
& ","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

On the form I have 76 unbound textboxes. The forms control sorce is
set
to
AllBalancesByFCandDB, and the onload event reads like this.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

:

No you cannot use the Sum function in VBA; it's only for query or for
control's ControlSource on form or report.

What error message or result did you get from the DSum function?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I couldnt get dsum to work. Is it not possible to do a sum in the
case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it
be
defined?
Also, why cant you just put a textbox in the header or footer of the
form
and sum the textbox in the detail? I know its unbound but its
showing
values.
--
Please remember to mark this post as answered if this solves your
problem.


:

Try using a DSum function to calculate the sum of the desired field
from
the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event when
you
do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The control source of the form is a crosstab query, and column
headings
part
of the crosstab query can vary. With this in mind, there is no
way
to
do
the
sum on the form. Is it possible to do the sum in the query? I
already
populate all the controls on the form with the load event, if the
sum
was
included in the query I could do the same with it. Something
like
this.
sum(rst.Fields(i).Name)


:

I assume that DB1 is the textbox name on the form? Sum (and
other
aggregate
functions) work only wth fields, not with controls, as its
argument.
Therefore, if DB1 is a control, you cannot Sum on its values.
You
must
Sum
on the values of one or more fields that are in the form's
Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field --
whatever
its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms
footer
and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query
like
this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource =
sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
Error on my part -- wrong field to be summed:

=DSum("AllCharges.BalDue", "AllCharges", "InStr("",""" &
[Forms]![Parameters].[LocationFilter] & ""","","","" & [db] & "","")>0")


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ken Snell (MVP) said:
OK, I think I'm understanding what you want. You want a sum of the values
in all the fields in all the columns --- the DB "column".

I believe there is no need for you to use the crosstab query directly for
your data. You can get the result directly from the AllCharges
table/query, using the same WHERE filter as in the crosstab query.

Try this for your unbound textbox's RowSoure expression:

=DSum("DB", "AllCharges", "InStr("",""" &
[Forms]![Parameters].[LocationFilter] & ""","","","" & [db] & "","")>0")
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ryan said:
=DSum("DB","AllBalancesByFCandDB") didn't work either. I will go ahead
and
mark this as answered since you have stuck with me for so long. If you
come
up with any other possible solutions please let me know. It just doesn't
make sence that you can't just sum the unbound textbox, but oh well.


Ken Snell (MVP) said:
The DSum expression should be this:

=DSum("DB","AllBalancesByFCandDB")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



It says #Name? I think the problem is that the field im wanting to
sum is
the column heading field in the crosstab, which can produce 1-76
columns.
=Dsum([DB],[AllBalancesByFCandDB])
Here is the Crosstab Query

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Financial Class
Total]
FROM AllCharges
WHERE (((InStr("," & [Forms]![Parameters].[LocationFilter] & ",","," &
[db]
& ","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

On the form I have 76 unbound textboxes. The forms control sorce is
set
to
AllBalancesByFCandDB, and the onload event reads like this.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

:

No you cannot use the Sum function in VBA; it's only for query or for
control's ControlSource on form or report.

What error message or result did you get from the DSum function?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I couldnt get dsum to work. Is it not possible to do a sum in the
case
statement like
Select Case j
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.GtotalDB1.ControlSource = Sum(rst.Fields(i).Name
The above tells me that the Sub or Function is not defined. Can it
be
defined?
Also, why cant you just put a textbox in the header or footer of
the
form
and sum the textbox in the detail? I know its unbound but its
showing
values.
--
Please remember to mark this post as answered if this solves your
problem.


:

Try using a DSum function to calculate the sum of the desired
field
from
the
crosstab query:

DSum("NameOfField", "NameOfCrosstabQuery")

You could calculate this expression in the report's Open event
when
you
do
the other assignments/updates.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


The control source of the form is a crosstab query, and column
headings
part
of the crosstab query can vary. With this in mind, there is no
way
to
do
the
sum on the form. Is it possible to do the sum in the query? I
already
populate all the controls on the form with the load event, if
the
sum
was
included in the query I could do the same with it. Something
like
this.
sum(rst.Fields(i).Name)


:

I assume that DB1 is the textbox name on the form? Sum (and
other
aggregate
functions) work only wth fields, not with controls, as its
argument.
Therefore, if DB1 is a control, you cannot Sum on its values.
You
must
Sum
on the values of one or more fields that are in the form's
Recordset,
which,
from your code, would appear to be "rst.Fields(i)" field --
whatever
its
name is.

So the expression would be
=Sum(NameOfTheFieldToWhichDB1IsBound)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have a form that gets its data dynamicly with this code,
Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

My problem is that if I throw an unbound textbox on my forms
footer
and
try
to sum one of the results like this
=Sum([DB1])
it gives me #Error. I also tried doing a sum in the Query
like
this
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Me.DB1GTotal.ControlSource =
sum(rst.Fields(i).Name)
And then like this
Me.DB1GTotal = sum([DB1GTotal])
Is it possilbe to get the sum of DB1 on my form?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top