Query Select Field as String

[Vidar13]

Is it possible to incorporate a field lookup in a SQL select statement?

What I'm trying to do is have a select query return the results from a field name that is specified in a form.
To make matters slightly more complicated, I'm only looking up the last digit (number) for the fieldname:

So, to return the value of ItemType1 or ItemType2 or ItemType3 (all fields in my table), I want to create a select statement that concantenates the known "ItemType" with the 1, 2 or 3 specified in my form field

So, I would think you could use "

ValueField: "[ItemType" & [forms]![formname]![numberfield] & "]"

But, of course, this just returns the value "ItemType2", "ItemType3", etc, instead of the actual field's contents.

If I omit the quotes, Access auto-place the brackets around ItemType and thinks that's the field I'm after.

Is there a way to overcome this?

 

[princeshri]

use

"[" & ItemType & [forms]![formname]![numberfield] & "]"

HTH,


Shri

 

[Vidar13]

If [forms]![formname]![numberfield] is 2 then with your syntax, it's returning:


[2]


Apparently ignoring the ItemType and assuming it is a field itself. What I want the select statement to yield is

ValueField: [ItemType2]

Where, the form's field value is assertained as being 2

 

[Stickarm]

"[ItemType" & [forms]![formname]![numberfield] & "]"
would this work?

 

[Vidar13]

That's exactly what I tried in the first place (see initial post above). It just returns the name of the field, not the value of the field.