Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Do you wish to do this in VBA or a query or maybe on a form?

  3. #3
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Simple.

    [To Order]+ Choose(weekday(date()), [Sun Safety], [Mon Safety], [Tue Safety], [Wed Safety], [Thu Safety], [Fri Safety], [Sat Safety]) -[On Hand]
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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?
    Last edited by MarkLiquorman; 2012-10-20 at 14:27.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    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. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by JeanM View Post
    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    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
  •