How to get field variables stored in a string to return the values of the fields

[Rokhi]

Hi

The problem

VBA - DAO
How do I extract a value from a string containing field names

Data:
rst1!data1 = "ABC"
rst1!data2 = "000123000"

query Fields sourced from a lookup:
FldVarString = "Nz(rst1![Data1]) & mid(rst1!Data2],3,3)" (or any of a number of combinations)

I want another variable, fldResultString to contain "ABC123"
I don't want to use dlookup or any other query-based method as this is an iterative calculation.
I thought EVAL() might work but it doesn't.

Cheers

 

[Guus2005]

Could you rephrase the questions and get to the point a bit quicker? Also, could you generalise and not be so specific?

Be more specific to clarify your problem when asked. You'll get more response if you do

Since you are using VBA to walk through the recordset, there is no need to cramp your VBA statements together. Use If then else(if) endif instead of IIF. It is more readable and easier to debug. Use IIF in queries, not in VBA.

HTH

 

[Rokhi]

Hey, thanks for the advice


The statements were cramped in the examples to save space in the post

The iif() is used because I'm storing the calculation in a single string variable, that's the point.

I want to be able to call a stored line of query logic from a lookup and use it in the code as is, without having to construct the entire logic from what would have to be a number of source fields containing a variable number of field names, operators and conditions like iif() to acheive different field layouts depending on the calling method's requirements.

I accept that I may be approaching this in the wrong way altogether though, so any alternative methods of achieving the goal would be welcomed.

The broad goal is to use a single piece of code to return different sets of values from any of a number of different tables.

Thanks again for your input. I have generalised the post as suggested.

 

[Guus2005]

The command you are looking for in VAL()

FldVarString = Nz(rst1![Data1]) & val(rst1!Data2])

VAL returns a number. If your input is "00120 39asjdns28283", the output is 12039

HTH

 

[Rokhi]

fldVarString does not contain direct references to the data fields, but a string containing the references.

ie - not

FldVarString = Nz(rst1![Data1]) & val(rst1!Data2])

but

FldVarString = "Nz(rst1![Data1]) & val(rst1!Data2])"

In which case, if I try to assign the value

fldVarReturnVal = fldVarString

fldVarReturnVal will contain the string "Nz(rst1![Data1]) & val(rst1!Data2])"
not the values of the 2 fields.
I want to get the field values.

 

[Guus2005]

How did that string "Nz(rst1![Data1]) & val(rst1!Data2])" happen in the first place.
Why was it not interpreted? If it is by design, probably you need to change your design.

Using EVAL() would be my best guess. You need to make sure that all objects are valid i.e. that rst1 exists and is not EOF.

If this doesn't solve your problem please post a sample database (zipped)

HTH

 

[Rokhi]

I am trying to create a single method that can manipulate whatever table is presented to it in different ways depending on some passed parameters.

Table1 may have fields data1 and data2, and table2 may have fields otherdata1, otherdata2 and otherdata3 and so on.

If the method is dealing with table1, and condition a exists, I might want to return the concatenated value of data1 & data2. If table2 is used, then if condition b exists, I might want to return the concatenated values of otherdata1 & mid(otherdata2,2,2) & otherdata3 or maybe it will be a math calculation (otherdata1 * otherdata2 for instance)

The range of tables and conditions will change over time, so I want to store the various changing calculation control strings in another table (call it tblControl), so the method can call them depending on the table in use and the condition, and use that string to control the calculation that will return the results.

When the method picks up the control string from tblControl, it will (possibly) look like this for table1, option a:
"nz(!data1) & nz(!data2)"

or this for table2 option b:
"nz(!otherdata1) & mid(nz(!otherdata2),2,2,2) & nz(!otherdata3)"

or conceivably, for table x option y
"(nz(!Differentdata3) * nz(!Differentdata6)) mod 10"

I can't think of any other way to pass a stored field calculation specification to a program for evaluation.

If I was dealing with local variables, (say var1 and var2 containing "ABC" & "123" respectively) then the stored specification "var1 & '/' & var2" would evaluate using EVAL. After assigning the relevant specification string from tblControl to another local variable - say strVarStr, Eval(strVarStr) would return "ABC/123".
The difficulty arises trying to evaluate the values from a local recordset created from the required table because EVAL can't find the recordset.

 

[Guus2005]

...

If this doesn't solve your problem please post a sample database (zipped)

HTH

I am trying to recreate your problem, me thinks better you post a sample.