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?
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?