Using a field name as variable in a query

  • Thread starter Thread starter Titlepusher
  • Start date Start date
T

Titlepusher

I need to use a field name as a variable in a query.

I have a table "Contractor List table" with forty-some Yes/No fields titled
by County names. (field 6 name = Hamilton, field 7 name = Johnson, etc...)
(the first 5 fields are the contractor name, phone, address, etc)

A form contains a drop down with the field names of the table. I want to
design a query to show records where [form..drop down] = -1 (yes). The
purpose is to find contractors who will work in a specific county.

So, to restate I want to query like .. if "field name" = -1 then show all
records.

I thought I could do this, but cannot make the query work. Any help is
appreciated.

Thank you,
tp
 
You are committing spreadsheet with a relational database. Rethink your
structure. You need three tables:
Contractor:
Contractor ID (autonumber PK)
your 5 fields of contractor attributes
County
CountyID (Autonumber PK)
CountyName
ContractorCounty
ContractorID
CountyID

The last table needs a compound PK on ContractorID and CountyID

Your drop-down can thn show the County Names from the County table, without
the need to reference field names in a query.

-TedMi
 
Back
Top