By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,929 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

dlookup or find methods for fileds using balue for field name

P: 3
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#)
Oct 22 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
Maybe you have more than resulting value in your R2?

In fact you obtain a string instaed date? Is it the problem?
Oct 27 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.