Passing and using field name strings to vba

[Guest]

I have created a function to count the number of times that a field value
changes from a value of 0 to 1. What I am having trouble with is passing the
name of the field to the function and then using argument in the code. He is
what I have so far:

table name is alarms
field name is INFEED_1

my function call is
=CountChanges([INFEED_1])

my function is:

Public Function CountChanges(strField As String) As Integer
Dim intCount As Integer
Dim intLastValue As Integer

Set rst = CurrentDb.OpenRecordset("alarms")
rst.MoveLast
rst.MoveFirst
intLastValue = 0
CountChanges = 0

Do While Not rst.EOF
If rst!strField = 1 And intLastValue = 0 Then
CountChanges = CountChanges + 1
End If
intLastValue = rst!strField
rst.MoveNext
Loop
Set rst = Nothing
End Function

If I remove the variable strField and simply enter [INFEED_1] it works fine
but I can't seem to pass and use the name of the field.

Thanks in advance for your help.

 

[Douglas J. Steele]

rst.Fields(strField)

 

[chris.nebinger]

Douglas gave the right answer, but let me throw a few things out there:

You need to include a sort on the table that you are opening. If not,
then you are not guaranteed that it will open in the same order every
time.

Set rst = CurrentDb.OpenRecordset("Select [" & strfield & "] from
alarms order by ALARMDATE")

Also, these two lines:
rst.MoveLast
rst.MoveFirst

cause the recordset to jump to the end, then back. The only time that
you ever should do that is on large datasets just to get a record
count. If that was the case, then it is usually faster to get the
recordcount by using a COUNT group by clause in a seperate recordset.
Anyway, looking at your code, you can remove those to get a speed
increase, albeit you might not notice depending on your data size.


Chris Nebinger

rst.Fields(strField)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have created a function to count the number of times that a field value
changes from a value of 0 to 1. What I am having trouble with is passing
the
name of the field to the function and then using argument in the code. He
is
what I have so far:

table name is alarms
field name is INFEED_1

my function call is
=CountChanges([INFEED_1])

my function is:

Public Function CountChanges(strField As String) As Integer
Dim intCount As Integer
Dim intLastValue As Integer

Set rst = CurrentDb.OpenRecordset("alarms")
rst.MoveLast
rst.MoveFirst
intLastValue = 0
CountChanges = 0

Do While Not rst.EOF
If rst!strField = 1 And intLastValue = 0 Then
CountChanges = CountChanges + 1
End If
intLastValue = rst!strField
rst.MoveNext
Loop
Set rst = Nothing
End Function

If I remove the variable strField and simply enter [INFEED_1] it works
fine
but I can't seem to pass and use the name of the field.

Thanks in advance for your help.- Hide quoted text -- Show quoted text -