Creating fields in a query (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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])

Reply | Quote

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

Reply | Quote

If ProductCode is the primary key of the table and if it is a number field, you could use

Qty1: DLookup("Day" & Weekday(Date(),7),"NameOfTable","ProductCode=" & [ProductCode])

If it is the primary key and if it is a text field:

Qty1: DLookup("Day" & Weekday(Date(),7),"NameOfTable","ProductCode=" & Chr(34) & [ProductCode] & Chr(34))

where NameOfTable is the name of – you guessed it. Similar for Qty2 and Qty3. But this will be slow.

Reply | Quote