Use a string to define a field

[Mark]

I have the following function:

Public Function LastMonthDDD(dddMonth As Long, dddYear As Integer, wicket As
String) As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryDDD Recap", dbOpenDynaset)

rst.MoveFirst

If dddMonth = 10 And dddYear = 2009 Then
Do While Not rst.EOF
If rst("ActionMonth") > 6 And rst("ActionMonth") < 10 And
rst("ActionYear") = 2009 Then
LastMonthDDD = LastMonthDDD + rst("""& wicket & """)
Count = 1
End If
rst.MoveNext
Loop
Else
If dddMonth = 1 Then
dddMonth = dddMonth + 12
dddYear = dddYear - 1
End If

Do While Not rst.EOF
If rst("ActionMonth") = (dddMonth - 1) And rst("ActionYear") =
dddYear Then
LastMonthDDD = LastMonthDDD + rst("""& wicket & """)
Count = 1
End If
rst.MoveNext
Loop

End If

If Count = 0 Then
LastMonthDDD = 0
End If

End Function

I want to use a string to define the column from the query I want to total
instead of establishing a function fo reach column in the query. This is
the line that is giving me trouble, LastMonthDDD = LastMonthDDD + rst("""&
wicket & """). I have made it work buy doing this, LastMonthDDD =
LastMonthDDD + rst("JAN")

Little help!

Thanks

 

[Dirk Goldgar]

I have the following function:

Public Function LastMonthDDD(dddMonth As Long, dddYear As Integer, wicket
As
String) As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryDDD Recap", dbOpenDynaset)

rst.MoveFirst

If dddMonth = 10 And dddYear = 2009 Then
Do While Not rst.EOF
If rst("ActionMonth") > 6 And rst("ActionMonth") < 10 And
rst("ActionYear") = 2009 Then
LastMonthDDD = LastMonthDDD + rst("""& wicket & """)
Count = 1
End If
rst.MoveNext
Loop
Else
If dddMonth = 1 Then
dddMonth = dddMonth + 12
dddYear = dddYear - 1
End If

Do While Not rst.EOF
If rst("ActionMonth") = (dddMonth - 1) And rst("ActionYear") =
dddYear Then
LastMonthDDD = LastMonthDDD + rst("""& wicket & """)
Count = 1
End If
rst.MoveNext
Loop

End If

If Count = 0 Then
LastMonthDDD = 0
End If

End Function

I want to use a string to define the column from the query I want to total
instead of establishing a function fo reach column in the query. This is
the line that is giving me trouble, LastMonthDDD = LastMonthDDD +
rst("""&
wicket & """). I have made it work buy doing this, LastMonthDDD =
LastMonthDDD + rst("JAN")

Little help!

Thanks

So the variable <wicket> holds the name of the field? Then all you need is:

LastMonthDDD = LastMonthDDD + rst(wicket)

A string variable, argument, or expression can be used directly as an index
into the recordset's Fields collection.