Use a string to define a field


M

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
 
Ad

Advertisements

D

Dirk Goldgar

Mark said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top