![column] AS variable

[Tim]

I can't figure out how to use a variable in place of a
![column] statement in a With statement. In the following
code, I would like to replace [m1] with a variable so that
I can use the same code on different columns by changing
the variable rather than reusing the entire code. If I
try using a string variable in place of [m1] it doesn't
work. Do I need to declare a different type of variable?
Here is the code that I'm trying to use:

Do While myCount <> 0
With MyTbl
If ![Type] = dType Then
If ![m1] = colVal Then
mrank = mrank
Else
mrank = mrank + 1
End If
Else
If ![Type] <> dType Then
mrank = 1
dType = ![Type]
End If
End If
colVal = ![m1]
..Edit
![m1Rank] = mrank
..Update
..MoveNext
End With

myCount = myCount - 1

Loop

 

[Allen Browne]

May need to avoid the With block, but you can use:
MyTbl("MyField")
so
strFieldName = "MyField")
MyTable(strFieldName)

 

[thad]

I didn't know you could do it that. I learn something
every day. I typically use

rs.Fields(strFieldName)

-----Original Message-----
May need to avoid the With block, but you can use:
MyTbl("MyField")
so
strFieldName = "MyField")
MyTable(strFieldName)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

I can't figure out how to use a variable in place of a
![column] statement in a With statement. In the following
code, I would like to replace [m1] with a variable so that
I can use the same code on different columns by changing
the variable rather than reusing the entire code. If I
try using a string variable in place of [m1] it doesn't
work. Do I need to declare a different type of variable?
Here is the code that I'm trying to use:

Do While myCount <> 0
With MyTbl
If ![Type] = dType Then
If ![m1] = colVal Then
mrank = mrank
Else
mrank = mrank + 1
End If
Else
If ![Type] <> dType Then
mrank = 1
dType = ![Type]
End If
End If
colVal = ![m1]
.Edit
![m1Rank] = mrank
.Update
.MoveNext
End With

myCount = myCount - 1

Loop

.