dlookup or find methods for fileds using balue for field name

[BobM]

i read the thread entitled "Convert Field Value String to Actual Field in Expression"

It appeared that the final solutions got moore complex not less complex

MY PROBLEM IS:
1. get a value from one table as a string
2. lookup the value of a field in a second table
where the field name = the value of field in table 1

I CAN GET THIS USING DLOOkUP, BUT NOT BY FIND METHOD
example:
c = value from tableR2 (if [PART] - "AN" then c = "HD1")
cx = date from tableR!
(find record using findfirst and get value from filed "HD1")




HERE IS MY CODE
Dim CX As Variant 'Specific HX DATE
Dim C As Variant
DIM SQL as String



Dim R1 as Recordset
' HX_DATES (PATID as Long), HD1, HD2 HD3 HD4 as Dates (double)
Dim R2 as Recordset
'HX_LIST ( PART as string) LIST OF HX VALUES
C = Null
CX = Null
SQL = "Select * from HX_LIST where [PART] = 'AN' ;"



Set R1 = DB.OPENRECORDSET("HX_DATES", Db_readonly)
SET R2 = DB.OPENRECORDSET (SQL)

PROBLEM:
Moving to first record of R2 gets value of "H01" (this is a string)
As example R2!H01 = #2/3/2006#
=========
THIS WORKS: CX = DLookup("" & C & "", "tmp_hex", "patid = " & PATID)

WHAT I WANT IS
R1.findfirst "PATID = " & PATID (This works)
C = "R!" & C (Makes C = R1!H01)
CX = R1!H01 (I want CX = #2/3/2006#)

[PEB]

Maybe you have more than resulting value in your R2?

In fact you obtain a string instaed date? Is it the problem?