Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating fields in a query (2000)

    I have the following fields in a table:
    ProductCode, Day1, Day2, Day3, Day4, Day5, Day6, Day7, Day8, Day9, Day10, Day11, Day12, Day13, Day 14

    I need to create a query which will create 3 outputs fields QtyOne, QtyTwo and QtyThree

    The field calculation I have at the moment is QtyOne: "Day" & Weekday(Date(),7)
    The ,7 makes the weekday calculation think that Saturday is the first day of the week.

    As today is Monday, this produces the output Day3

    For QtyTwo I will use "Day" & Weekday(Date(),7)+1
    and for QtyThree I will use "Day" & Weekday(Date(),7)+2

    Problem is, how do I make QtyOne equal to the contents of the field Day3, QtyTwo equal to the contents of the field Day4 and QtyThree equal to the contents of the filed Day5 rather than the string values.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating fields in a query (2000)

    You can use the Choose function:

    Qty1: Choose(Weekday(Date(),7),[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7])

    Qty2: Choose(Weekday(Date(),7),[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day8])

    Qty3: Choose(Weekday(Date(),7),[Day3],[Day4],[Day5],[Day6],[Day7],[Day8],[Day9])

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating fields in a query (2000)

    Thanks Hans
    I'd never even considered the idea of using a choose function in a query. I may need to retrun to the idea of converting a string to a field name later, so if you have any pointers plese let me know.
    Many thanks
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating fields in a query (2000)

    If ProductCode is the primary key of the table and if it is a number field, you could use
    <code>
    Qty1: DLookup("Day" & Weekday(Date(),7),"NameOfTable","ProductCode=" & [ProductCode])
    </code>
    If it is the primary key and if it is a text field:
    <code>
    Qty1: DLookup("Day" & Weekday(Date(),7),"NameOfTable","ProductCode=" & Chr(34) & [ProductCode] & Chr(34))
    </code>
    where NameOfTable is the name of - you guessed it. Similar for Qty2 and Qty3. But this will be slow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •