Results 1 to 8 of 8
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Auto-update of date field (2002 SP-2)

    Poor "subject" title, but the best I can do.
    I've been thinking about this issue since a problem arose this Monday. In a continuous form (filtered by date ranges) I have an order status field (txtOrderStatus) with conditional formatting. One of the (several) status' is "HOLD". Let's say an order is placed for shipment today (5/21/04), but is then placed on hold. The conditional formatting makes the order stand out like a sore thumb today. However, when Monday morning rolls around (because the form is usually filtered for the current weeks orders - by using Filter by Form) the order placed on hold does not appear. If I remember at the end of the day to review any orders on hold and change the date to at least some day after today, it's not a problem; the problem is remembering to do it. Hmm, actually if the order with toays date (Fri) it would need to be set to Sun to appear next week. (qfltOrdersCurrentWeek is Sun-Sat).

    I should say that the form is first opened with all orders for the current year (qfltOrdersCurrentYear), far too many orders to even consider viewing, and then a filter is chosen from Filter by Form (qfltOrdersCurrentWeek, qfltOrdersThisNextWeek, etc.). I'm guessing what I need to do is address the issue in qfltOrdersCurrentYear, but I'm not certain how to approach it. If txtOrderStatus is "HOLD" and dtmShConDate1 is less than today; change dtmShConDate1 and dtmShConDate2 to today. I do not want to change the date on future orders (even if on hold status). I presume I would need to change the query to an update query, but I just can't seem to wrap my pea-brain around the concept. Any ideas?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto-update of date field (2002 SP-2)

    Would a simple solution be to change your filter to orders for the last 7 days or something simila? That way an order put on hold on Friday would still show up on Monday or Tuesday.
    Wendell

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Auto-update of date field (2002 SP-2)

    Wendell:

    Thanks for the reply. I actually have a number of filters that would include that range (ie. Date()-3 through Date()+7) but I run into the same problem if I fail to change the dates on the "hold" orders; as soon as the date criteria goes out of range (and I forget to change the dates) I loose the records. As a transportation broker, I am a little like an air traffic controller; right now some of my planes are circling the airport, some are at the terminal, some are landing, and some are taking off. The problem I'm having is remembering those planes in a holding pattern. My issue isn't burning wreckage, but failed customer service <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Auto-update of date field (2002 SP-2)

    How about filtering records within the current week OR with "Hold" status? You can do this in a form filter by using the Or tab at the bottom of the window, or in a query by entering criteria in two different rows.

    Two criteria in the same row act as AND criteria - both must be satisfied. Two criteria in different rows act as OR criteria - one or the other (or both) must be satisfied.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Auto-update of date field (2002 SP-2)

    Hans:

    <img src=/S/munch.gif border=0 alt=munch width=19 height=17>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Auto-update of date field (2002 SP-2)

    Hans:
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    That actually works quite well (to some degree <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I like the fact that I don't need to be messing with the recordset (date changes) and it's a simple solution. The one remaining issue (however) is that the form is sorted by decending date, so the hold order is always at the bottom and out of view. Still, it is workable to some degree and much better than what I had.
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Auto-update of date field (2002 SP-2)

    If you like, you can sort by txtOrderStatus first (ascending or descending), or by a calculated field:

    OnHold: [txtOrderStatus]="HOLD"

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Auto-update of date field (2002 SP-2)

    Hans:

    Depending on the filter, that didn't quite work out either (there are up to 12 different status for an order). But now that you have given me a way to include the "Hold" orders in any of the filters (see thread) I took the liberty to create a txtbx in the header with =Abs(Sum([txtOrderStatus]="HOLD")) and conditional formatting. Works for me! Thanks for the help friend!
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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