Hello; I am needing to use a vari

[JeffCurtis]

Hello;
I am needing to use a variable to reference a particular field name. Depending on the variable Value I want to be able to decide which field to read in.

Database:
Pigment# Red Blue Green
501 5 6 2
505 7 5 3
507 7 1 4

For Example, if the Current variable Color = "Red"

if rs1![Pigment] = "501" then
Amt = rs1[Color] ' At this moment = "Red"
end if

Amt = 5

This seems like a real basic question....but I'm pulling my hair out
to figure it out...Any ideas...Thanks...jch

 

[KJBarrie]

Seems as if you want to use a Select Case statement, but I'm unsure I understand exactly what you are doing. You need to list all conditions and deal with each.

Select Case rs1!Pigment
case "501"
amt = rs1(Color)
case "505"
'Do this
case Else
'Remainder
End Select