Using Expressions as Field Names in a Query

[kevinegg45]

I have a table which contains the name of an item, and 52 Yes/No fields (one for each week of the year) to indicate which weeks the item relates to.



I now want to be able to select the records which relate to a specific week.

I know that I can create 52 separate queries, one for each week, but this seems a lengthy and unwieldy way of doing things.

What I would like to do is to have one query, which asks me for a week number and then selects the records from that week number field.

I know how to get a query to ask for input, and also how to convert this to a string expression which matches a field name, but I cant work out how I can then use this expression as a field name.

Can anyone help me with this.

I have a little knowledge of Macro's, SQL, and VBA, so any of these options would be helpful.

Or maybe there is another way of doing it by designing the table differently?

Kevinegg45

[RuralGuy]

52 Yes/No fields has a problem. How about just 2 fields? 1) Name of an item, 2) a number from 1 to 52.

[kevinegg45]

52 Yes/No fields has a problem. How about just 2 fields? 1) Name of an item, 2) a number from 1 to 52.

Thanks RuralGuy,

Unfortunately,I don't think this will work, as each record can have a 'Yes' in more than 1 week (Could be none, any or all of the week number fields)

KevinEgg

[orange]

I suggest you research Normalization and adjust your table structure accordingly.

It sounds like you have an ItemID and ItemUseDate (to me at least -- best guess).
Your query could become(something like)

Select ItemID , ItemUseDate from tblItemUsage
Where Datediff("ww",YearStartDate,ItemUseDate) = [Enter Week Number]

More info on DateDiff at http://www.techonthenet.com/access/f...e/datediff.php

You may also want to review the info at
http://en.wikipedia.org/wiki/ISO_week_date

Good luck