Sum different Columns

  • Thread starter Thread starter LeftyLeo via AccessMonster.com
  • Start date Start date
L

LeftyLeo via AccessMonster.com

This database has a table tblBudget with columns: fldLocation, P01, P02,...
P13, fldYear. I inherited this and need to make changes to create a Total
Carryover field on a form. To do this I need to get the values for a series
of columns based on date entered on form. I am really not sure how to do
this and whether an array is the way to go or a while...wend loop?

Here is the code I have so far:

Private Sub txtDate_Exit(Cancel As Integer)

Dim rec As ADODB.Recordset
Dim Balance As Double
Dim TtlBalance As Double


If Not IsNull(Me.txtDate.Value) Then
Me.txtPeriod = Right(sisCurPeriod(Me.txtDate), 2)
Me.txtYear = sisCurYearFiscal(Me.txtDate)
End If

If Not IsNull(Me.cboLocation.Value) And Not IsNull(Me.txtPeriod.Value) And
Not IsNull(Me.txtYear.Value) Then

While Me.txtPeriod > 0

Set rec = New ADODB.Recordset
rec.Open "Select tblBudget.[P" & Me.txtPeriod.Value & "] from tblBudget
where tblBudget.fldLocation = '" & Me.cboLocation.Value & "' And tblBudget.
fldYear =" & Me.txtYear.Value & ";", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

If Not rec.EOF Then
rec.MoveFirst

Balance = rec("P" & Me.txtPeriod.Value)
TtlBalance = TtlBalance + Balance
rec.Close
Wend
Let Me.txtPeriod = String$(2 - Len(Me.txtPeriod - 1), 48) & Me.txtPeriod -
1
end if
end if

Any help would be appreciated and please forgive my ignorance in this area.

Anna
 
LeftyLeo said:
This database has a table tblBudget with columns: fldLocation, P01, P02,...
P13, fldYear. I inherited this and need to make changes to create a Total
Carryover field on a form. To do this I need to get the values for a series
of columns based on date entered on form. I am really not sure how to do
this and whether an array is the way to go or a while...wend loop?

Here is the code I have so far:

Private Sub txtDate_Exit(Cancel As Integer)

Dim rec As ADODB.Recordset
Dim Balance As Double
Dim TtlBalance As Double

If Not IsNull(Me.txtDate.Value) Then
Me.txtPeriod = Right(sisCurPeriod(Me.txtDate), 2)
Me.txtYear = sisCurYearFiscal(Me.txtDate)
End If

If Not IsNull(Me.cboLocation.Value) And Not IsNull(Me.txtPeriod.Value) And
Not IsNull(Me.txtYear.Value) Then

While Me.txtPeriod > 0

Set rec = New ADODB.Recordset
rec.Open "Select tblBudget.[P" & Me.txtPeriod.Value & "] from tblBudget
where tblBudget.fldLocation = '" & Me.cboLocation.Value & "' And tblBudget.
fldYear =" & Me.txtYear.Value & ";", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

If Not rec.EOF Then
rec.MoveFirst

Balance = rec("P" & Me.txtPeriod.Value)
TtlBalance = TtlBalance + Balance
rec.Close
Click to expand...
End If
Let Me.txtPeriod = String$(2 - Len(Me.txtPeriod - 1), 48) & Me.txtPeriod -1
Wend

end if
'end if

Any help would be appreciated and please forgive my ignorance in this area.

Anna
Click to expand...

I figured this out this morning. One: I was mixing up my variables (numbers
and text) this line:While Me.txtPeriod > 0 should be While Me.txtPeriod <>
"00", Then needed the end if before the rec.close and finally the Let Me.
txtPeriod = String$(2 - Len(Me.txtPeriod - 1), 48) & Me.txtPeriod -1 needs to
be inside the while wend loop (duh). Thought I would let anyone interested
know.

Thanks,
 
Back
Top