Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display message depending on number of days (Access 2003)

    I have a situation where a shipment must be made every 90 days. In the database I display, on the main switchboard, the number of days until a shipment must occur. I now need to display a message on the switchboard, and change it every "x" number of days. For example, between 60 and 90 days until the next shipment, message A is displayed. Between 40 and 59 days, message B is displayed. Between 20 and 39 days, message C is displayed, and so on, down to zero. I created a table which contains the upper value of these ranges (90, 59, 39 and so forth) as well as the message which I need to display. These messages will change from time to time as will the ranges when they need to appear. How do I display the appropriate message on the switchboard? I am calculating the number of days until the next shipment by using this formula
    =Abs(Date()-DMax("ShipDate ","tblShipment")-90)
    This is displayed in a control called txtCountdown
    I tried an SQL statement for the maximum value to see if I could get it working. It worked really well with a combo box, but it wont' work in a text box. I'm sure it's something simple, but I'm just not getting it! The SQL statement is below:
    SELECT [tblMessage].[Days], [tblMessage].[Message] FROM [tblMessage] where [tblMessage].[days]>=[txtcountdown].[value]

    Any help would be greatly appreciated.

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

    Re: Display message depending on number of days (Access 2003)

    Switchboard forms (I assume you are referring to the kind generated by the Access wizard) are normally unbound forms. But you could pull up the next ship date and put it in a text box that isn't visible. Then you could use that to select the appropriate message based on the number of days to that date using the DLookup function. With that you might want to use Immediate IF statements (IIF()) that would look something like:
    <font color=blue><font face="Georgia">Me!txtShipmentMsg=IIF([NextShipDate-Date()<30,1,IIF([NextShipDate-Date()>29 AND <60,2,IIF([NextShipDate-Date()>59 AND <90,3,4)))</font face=georgia></font color=blue>
    (That's air code, so it may not have the correct number of parentheses or the like.) Then I would use a combo box linked to a table that contains 4 messages and and primary key of 1 to 4. Hope this gives you some ideas. I personally would do this kind of thing in VBA, but if you aren't comfortable with that approach, this should give you the same result.
    Wendell

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display message depending on number of days (A

    Yes, I used the Access switchboard manager to create the switchboard, but I have made several modifications including the addition of a number of fields. I am probably more comfortable with VBA than Access functions, so feel free to suggest VBA solutions. Also, I can't hard-code the number of days until the next shipment (as you have in your IIF example) because it might change. The user wants the ability to change the time period and the associated message, and this application has to remain flexible. Lastly, I don't know the date of the NEXT shipment, I only know the date of the LAST shipment. I have the number of days remaining displayed on the form, but I can't seem to get an Access function (namely DLOOKUP) to recognize the value in this field and use it to look up the appropriate message. Maybe I'm setting it up incorrectly.

    My messages are stored in a table called tblMessage. I did not create a primary key for this table, but certainly could if it would help this process. How would this be structured using VBA? I think that might be an easier solution for me.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display message depending on number of days (A

    An idea I have....it probably requires some 'tweaking' from the Mods, but I believe it should work
    Use a label to display your message.

    If your form is unbound, you will first need to specify a record you wish to set the form's focus on (either through combo box, or list box). Then you can have a text box that shows the due date (one that the user can change), and a hidden text box, (we'll call it txtDueDate), with a formula something along the lines of =now()-[duedate]....then depending on when you want your form to update, you can use this code:

    Private Sub Form_Update()
    Select Case txtDueDate
    Case "90"
    Me.lblWarning = "This must be shipped in 90 days"
    Case "60"
    Me.lblWarning = "This must be shipped in 60 days"
    Case "45"
    Me.lblWarning = "This must be shipped in 45 days"
    Case Else
    'do nothing
    End Select
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Display message depending on number of days (A

    I attach a demo that uses a Dlookup in the oncurrent event for frmshipments.

    If more than 1 record matches the condition, Dlookup returns the first match, so I point the DLookup to a query that sorts the messages into ascending order by day limit.
    Regards
    John



  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display message depending on number of days (A

    That's perfect! Thanks so much for solving that one for me. I was getting frustrated!

Posting Permissions

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