# Thread: Calculated field in a query

1. ## Calculated field in a query

Hi

I have the following formula:
[To Order]+[Mon Safety]-[On Hand] all number fields.

I need to change [Mon Safety] according to the day of the week, for example, on Tuesday I need to alter the formula to reflect [To Order]+[Tues Safety]-[On Hand] and so forth. Is there a way to put in a parameter in the formula? I can manually change the query each time but I will be passing the database on to a novice access user.

J

2. Do you wish to do this in VBA or a query or maybe on a form?

3. Hi

Do not want to do in a form. Prefer to do in query. Will do in vba if that is my only choice.

J

4. Simple.

[To Order]+ Choose(weekday(date()), [Sun Safety], [Mon Safety], [Tue Safety], [Wed Safety], [Thu Safety], [Fri Safety], [Sat Safety]) -[On Hand]

5. Hi Mark

I tried your suggestion by altering the formula to fit my needs in the select query: PO Order: [NeedtoOrder]+Choose(Weekday(Date()),[Mon Safety],[Tues Safety],[Wed Safety],[Thur Safety],[Fri Safety])-[On Hand]
It did not calculate a number. The columns headings in the table are Mon Safety, Tues Safety, and so on. I was hoping that I could choose the safety I needed. Am I doing something wrong? I do have a Order Date field in the table.

Jean

6. Your original formula had [To Order], which you later changed to [NeedToOrder]. Could this be a factor? If any part of the equation has a null value (Need To Order, Safety, On Hand) then the result will be null.

Also, did you want this to calculate the safety based on today's date or the Order Date?

7. ## Calculated Field in a query

Hi Mark

Attached is a zipped database that I think would be easier then me going back and forth trying to example what I am trying to do.

Jean

8. Originally Posted by JeanM
Hi Mark

Attached is a zipped database that I think would be easier then me going back and forth trying to example what I am trying to do.

Jean
Jean,

Did you forget the attachment?

9. ## Calculated Field in a query

Hi Mark

Sorry about that I do not use this forum that often and I forgot to upload the attachment. The attached database is just the piece that you are helping me with. If you need more I can resend.

Jean

10. Your problem is how you constructed the Choose function. You wrote this:

Choose(Weekday(Date()),[Mon Safety],[Tues Safety],[Wed Safety],[Thur Safety],[Fri Safety])

Translating this into English, it says "Based on Today's Date, if the Weekday = 1 (which is Sunday), then use [Mon Safety]; if Weekday = 2 (which is Monday), then use [Tues Saftely], on up to 5. If the value of Weekday is 6 or 7, the Choose function returns a Null; which makes your equation equate to Null.

You need to do it this way:

Choose(Weekday(Date()), 0,[Mon Safety],[Tues Safety],[Wed Safety],[Thur Safety],[Fri Safety], 0)

11. ## Page Break in a Report and Entering a PO Number

Hi Mark

That is awesome--thank you so much with the help. I have another request. I have attached the database again. I have a PO and I am having trouble with page breaks!! I also wondered if there could be a way to type in a unique PO Number? I have some other issues with the PO report but I have them worked out in the original so ignore the pop-ups about unit price. I do have it popping up with the user entering a PO Number but if you enter in a number it is the same for all PO's.

Jean

12. Hi Mark

All set with page breaks. In design view it does not show the page breaks. In print preview it did show the breaks. As for the PO I hedging toward an automatic generation of PO numbers, not sure what I need to do to accomplish this. Any help is greatly appreciated.

Jean

#### Posting Permissions

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