Results 1 to 4 of 4
  1. #1
    kevinegg45 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    5

    Using Expressions as Field Names in a Query

    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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    52 Yes/No fields has a problem. How about just 2 fields? 1) Name of an item, 2) a number from 1 to 52.

  3. #3
    kevinegg45 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    5

    Using Expressions as Field Names in a Query

    Quote Originally Posted by RuralGuy View Post
    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

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-18-2013, 09:58 AM
  2. Replies: 1
    Last Post: 08-12-2011, 12:46 AM
  3. Query Entity Names, and Column Names
    By Accidental DBA in forum Queries
    Replies: 1
    Last Post: 07-22-2011, 02:38 PM
  4. IIF In Query that refers to other field names
    By JohnBoy in forum Queries
    Replies: 2
    Last Post: 06-18-2010, 11:41 PM
  5. Replies: 1
    Last Post: 05-17-2010, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums