Using contents of a field as a field name

[Deena]

I have a table with many checkbox fields representing
different services that a vendor provides. The fields
are named T1, T2, T3..... There is also a two field
table that relates the field names to text strings, i.e.
T1 Cleaning, T2 Inspection .....
On a form someone selects from a combo box one of the
text strings (ex: Inspection). I would like to use this
input to know that I want to search the T2 field of my
Vendor Table to see if T2 is true for that Vendor. I am
currently doing this by building a separate query for
each Tx field and then using a macro with conditional
statements to decide whether to run that particular query
or not. This is working, but I would think there would
be a more elegant method. I am not a Visual Basic
programmer, but can often follow, replicate, and modify
code. Any way to do this within Access' Queries and
Macros?

 

[Ted]

I would think that you could use the DLookup function as
the data source for a control. Normally, the first part
of the function is the field name (such as T2) that you
would want to search.

For example: Dlookup("[T2]","Vendor Table","[VendorID]
= " & Me!VendorID)

It seems that the only thing that you have to do
differently is build the first part of the string using
the value of your combo box. Something like:

Dlookup("'[" & Me!MyComboBox & "]'","Vendor
Table","[VendorID] = " & Me!VendorID)

I haven't tried this before, but I would think it would
work.

 

[Deena]

-----Original Message-----
I would think that you could use the DLookup function as
the data source for a control. Normally, the first part
of the function is the field name (such as T2) that you
would want to search.

For example: Dlookup("[T2]","Vendor Table","[VendorID]
= " & Me!VendorID)

It seems that the only thing that you have to do
differently is build the first part of the string using
the value of your combo box. Something like:

Dlookup("'[" & Me!MyComboBox & "]'","Vendor
Table","[VendorID] = " & Me!VendorID)

I haven't tried this before, but I would think it would
work.

-----Original Message-----
I have a table with many checkbox fields representing
different services that a vendor provides. The fields
are named T1, T2, T3..... There is also a two field
table that relates the field names to text strings, i.e.
T1 Cleaning, T2 Inspection .....
On a form someone selects from a combo box one of the
text strings (ex: Inspection). I would like to use this
input to know that I want to search the T2 field of my
Vendor Table to see if T2 is true for that Vendor. I am
currently doing this by building a separate query for
each Tx field and then using a macro with conditional
statements to decide whether to run that particular query
or not. This is working, but I would think there would
be a more elegant method. I am not a Visual Basic
programmer, but can often follow, replicate, and modify
code. Any way to do this within Access' Queries and
Macros?
.

.
Thanks, I will try this. It sounds like it will work.