Use the value in an Input Parameter as a Field Name

  • Thread starter Thread starter Ed Boulineau
  • Start date Start date
E

Ed Boulineau

Guest
Is it possible to internally develope the Name of a Field within a Query and use that Name to obtain the value contained in the field with that Name?

Hypothetical Example:

fieldname="Lv"&[xx] where [xx] value can be "01" thru "99"

Actual Field Names and Values in the table are:

Names: Lv01, Lv02, Lv03,..... Lv99
Values: ABC DEF GHI ZYX

For [xx] with value of "03" the Field Name would be Lv03
and the Value obtained should be "GHI".

I realize that this might require some modification in the SQL.
 
Yes. a simjple parameter query will do this where the parameter is defined as

"LV"& ("the numeric portion").

How you define numeric portion depends on where you're getting it from. Is it entered manualy, picked from a list, or referenced from a form? Without this info. I can't be more specific...
 
A parameter query will NOT work for this purpose. You can however create an SQL string dynamically using VBA and then run it.

A bigger problem is the structure of your table. You seem to have a 1-to-many relationship that you have "flattened" as though it were a spreadsheet. This data should be stored in two tables. The many-side table should contain the key of the original table plus the field identifier as a compound primary key. The third field would be the data value. So, instead of having 1 row with 99 columns, you would have 1 row in tableA and 99 related rows in tableB. Do some reading on database normalization to learn more about how to properly design tables. If you restructure your table, you'll need to change your method of input. You'll need a main form based on a query of tableA with a subform based on a query of tableB.
 
if a parameter query can't be used for this purpose, I have misunderstood what you are trying to do. Perhaps you can explain what you mean by "internally develop" and also expand on your table structure. Do you actually have field names LV01 through LV99 or are these in individual rows in a table with a single field name, which was my assumption?

If you do have 99 fields, as the previous posting says, you need to rethink your whole approach.
 

Users who are viewing this thread

Back
Top Bottom