Concatenation to variable names. Any Ideas?

[davec via AccessMonster.com]

I have 77 different fields that are named mth2 - mth78. I want to try and use
a incremental value from a for x = 2 to 78 and try and do something like
below, but my code does not work and I know that. Any ways around it except
for using a select case with a case for each instance. I am trying to
simplify code.

For X = 2 To 78
If rst1!Mth & X = rst2!Mth & X Then
Else
rstCompare!Mth = rst2!Mth & X - rst1!Mth & X
WriteOutRecord = True
End If
Next X

 

[Marshall Barton]

I have 77 different fields that are named mth2 - mth78. I want to try and use
a incremental value from a for x = 2 to 78 and try and do something like
below, but my code does not work and I know that. Any ways around it except
for using a select case with a case for each instance. I am trying to
simplify code.

For X = 2 To 78
If rst1!Mth & X = rst2!Mth & X Then
Else
rstCompare!Mth = rst2!Mth & X - rst1!Mth & X
WriteOutRecord = True
End If
Next X

The syntax for that kind of reference is:
If rst1("Mth" & X) = rst2("Mth" & X) Then

 

[davec via AccessMonster.com]

Thanks, that was smooth. Why is it that the answer is always right in front
of your face.

Again,
Thanks

I have 77 different fields that are named mth2 - mth78. I want to try and use
a incremental value from a for x = 2 to 78 and try and do something like

[quoted text clipped - 9 lines]

End If
Next X

The syntax for that kind of reference is:
If rst1("Mth" & X) = rst2("Mth" & X) Then