selecting a field in a query

[bravo6]

I have a table that I'm using in a query. The table has 24 fields which are Period01, Period02...Period24. Depending upon what the user selects in a form I want the appropriate field to be put into the field box in the query. The only thing I could find that works is a nested "IIF" statement such as:

period: IIf(([forms]![BSTO]![month])="JAN",([period12]),(IIf(([forms]![BSTO]![month])="FEB",([period11]),(IIf(([forms]![BSTO]![month])="MAR",([period10]).........

The problem is that once I get more than 5-6 "nested" statements, Access says that the statement is too complex or that it exceeds 255 charactor limit. How else can I choose which field goes into the quesry?

Thanks!!

 

[dhookom]

IMHO, your table structure is un-normalized and causing your issues. In a normalized table, the period/month would be a data value rather than a field name.

However, I expect you could use DAO code to modify the SQL of a saved query. Your code would probably run from the BSTO form and might look something like:

Dim strSQL as String
strSQL = "SELECT ... " & _
strSQL = strSQL & " FROM ... "
CurrentDb.QueryDefs("qselMyQuery").SQL = strSQL

You would need to write the logic into the code to dynamically map your field.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

dhookom is right. Your table is not normalized.
See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You have repeating column headings. Drop the number you have Period, Period, etc. This will create variable length records - not allowed in relational databases. This can lead to blank cells in the table - not good. Period01, Period02, etc. sounds like a category that could be a table. So you have a table within a table - not allowed.

You should get the point by now that the table structure is just plain designed wrong.