# Thread: Creating fields in a query (2000)

1. ## 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. ## 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. ## 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. ## 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
•