CODEWITHSUNDEEP
stringms-accessms-access-2010evalindirection
Zigo
Zigo

Reputation: 41

How to eval a field name contained in another field in an Access Query?

I need to create a long list of complex strings, containing the data of different fields in different places to create explanatory reports. The only way I conceived, in Access 2010, is to save text parts in a table, together with field names to be used to compose the string to be shown (see line1 expression in figure). Briefly:

//field A contain a string with a field name:
A = "[Quantity]"
//query expression:
=EVAL(A) 
//return error instead the number contained in field [Quantity], present in the query dataset

I thought doing an EVAL on a field (A), to obtain the value of the field (B) which name is contained in field A. But seems not working. Any way exist?

Example (very simplified): Sample query that EVAL a field containing other field names to obtain the value of the fields

Any Idea? PS: Sorry for my english, not my mothertongue.

Upvotes: 2

Views: 1207

Answers (1)

Zigo
Zigo

Reputation: 41

I found a interesting workaround in another forum. Other people had same problem using EVAL, but found that it is possible to substitute a string with a field contents using REPLACE function.

REPLACE("The value of field Quantity is {Quantity}";"{Quantity}";[Quantity])

( {} are used only for clarity, not needed if one knows that words to be substituted do not compare in the string). Using this code in a query, and nesting as many REPLACE as many different fields one want to use: 

REPLACE(REPLACE("<Salutation> <Name>";"<Salutation>";[Salutation]);"<Name>";[Name])

it is possible to embed fields name in a string and substitute them with the current value of that field in a query. Of course the latter example can be done more simply with a concatenation (&), but if the string is contained in a field instead that hardcoded, it can be linked to records as needed.

REPLACE(REPLACE([DescriptiveString];"[Salutation]";[Salutation]);"[Name]";[Name])

Moreover, it is possibile to create complex strings context-based as:

REPLACE(REPLACE(REPLACE("{Salutation} {Name} {MaidenName}";"{Salutation}";[Salutation]);"{Name}";[Name]);"{MaidenName}";IIF(Isnull([MaidenName]);"";[MaidenName]))

The hard part is to enumerate all the field's placeholders one wants to insert in the string (like {Quantity},{Salutation}, {Name}, {MaidenName}) in the REPLACE call, while with EVAL one would avoid this boring part, if only it was working.

Not as neat as I would, but works.

Upvotes: 2

Related Questions