use sql recordset column names that are similar with select case statement

Sorry about the bad title but I didn't know the best way to explain what is probably easy for the Experts.

This throws the error "Item cannot be found in the collection corresponding to the requested name or ordinal." and I know why but not how to fix it.

"mc_poverty_temp" is not a column in my SQL table but as you can see with my first select case I'm try to make life easier by building the field name I want to use in the table.

The second select case adds on a number to the end.  That completes the column name I want to use.

How do I get the code rsGetRecord("mc_poverty_temp")   to use   "mc_poverty_temp" variables value I built to get that column data?

For example, if the 1st select case choice is "HI" and the second select case choice is 2 then the table column name I need to get at is "pg_hi2".

Regards,
Torrey
Select Case rsGetStates("state_short")
	Case "AK"
		mc_poverty_state = "pg_ak"
	Case "HI"
		mc_poverty_state = "pg_hi"
	Case Else
		mc_poverty_state = "pg_rest"
End Select
 
 
Select Case Cint(mc_hh_members)
	Case 1
		mc_poverty_temp = mc_poverty_state + "1"
		If rsGetRecord("mc_poverty_temp") = False Then mc_valid = True
	Case 2
		mc_poverty_temp = mc_poverty_state + "2"
		If rsGetRecord("mc_poverty_temp") = False Then mc_valid = True
	Case 3
		mc_poverty_temp = mc_poverty_state + "3"
		If rsGetRecord("mc_poverty_temp") = False Then mc_valid = True
End Select

Open in new window

LVL 2
Torrey BatesPreidentAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
try without ""

like this

If rsGetRecord(mc_poverty_temp) = False Then mc_valid = True
0
 
Pratima PharandeCommented:
or if you cna chnage it ot numbers

If rsGetRecord(1)= False Then mc_valid = True
0
 
Torrey BatesPreidentAuthor Commented:
Thanks, duh on me!
0
All Courses

From novice to tech pro — start learning today.