Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    if statement (XP)

    Hi, I'd like to use a formula to update a date field. It has to do with the earliest date to refill a prescription based on utilization but I haven't ever used a formula or code or if statement for something like this so I need some help. IF Days_Supply <60, ERD (earliest refill date) = (0.75*Days_Supply)+LastFillDate OR IF Days_Supply >59, ERD=(0.9*Days_Supply)+LastFillDate.
    Would this go in an after update event or default value of the field or what? I tried playing with it as an event and as a default value but got errors with the event and got nothing when I put the formula (the first part) in as a default value. Thanks in advance.

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

    Re: if statement (XP)

    The Default Value property is not useful here, since it applies when the user creates a new record. When that happens, Days_Supply and LastFillDate are not filled in yet.
    You shouldn't use the After Update event of the form either, for this event occurs after the record has been saved. Changing a value causes the record to be saved again, and this in turn raises the After Update event again, so you end up in an endless loop.
    You need the Before Update event of the form for this. This is called just before the record will be saved.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Days_Supply < 60 Then
    Me.ERD = 0.75 * Me.Days_Supply + Me.LastFillDate
    Else
    Me.ERD = 0.9 * Me.Days_Supply + Me.LastFillDate
    End If
    End Sub

    If the actual names are different, you will have to modify the code accordingly.

  3. #3
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if statement (XP)

    Hans,

    Thanks but nothing happens when I enter this as a before update event as you guided me. I don't get an error, the field just stays blank. One question I forgot to ask is if Access adds dates like Excel does. The field "LastFillDate" is a date format.


    Private Sub NextFillDate_BeforeUpdate(Cancel As Integer)
    If Me.Days_Supply < 60 Then
    Me.NextFillDate = 0.75 * Me.Days_Supply + Me.LastFillDate
    Else
    Me.NextFillDate = 0.9 * Me.Days_Supply + Me.LastFillDate
    End If
    End Sub

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

    Re: if statement (XP)

    If you look carefully, you will see that I proposed that you use the Before Update event of the form, but you used the Before Update event of NextFillDate instead.

    Access, like Excel, internally stores dates as the number of days since 12/31/1899, so you can add a number of days to a date. By the way, if you want a whole number of days, you should use CLng(0.75 * Me.Days_Supply) and CLng(0.9 * Me.Days_Supply).

  5. #5
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if statement (XP)

    Hans, I moved the event to the form but still do not get any value or error in the NextFillDate field. Any other hints?

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Days_Supply < 60 Then
    Me.NextFillDate = 0.75 * Me.Days_Supply + Me.LastFillDate
    Else
    Me.NextFillDate = 0.9 * Me.Days_Supply + Me.LastFillDate
    End If
    End Sub

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

    Re: if statement (XP)

    1. Are there values in Days_Supply and LastFillDate?
    2. Did you change anything in the record? The Before Update event only occurs if the record has been modified in any way. If you move from one record to the next without changing anything, the Before Update event does not run.

    BTW, if NextFillDate is to be a "fixed" date that cannot be edited by the user, you don't need a NextFillDate field in the table, nor code. You can set the Control Source of the NextFillDate text box on the form to
    <pre>=IIf([Days_Supply]<60,0.75,0.9)*[Days_Supply]+[LastFillDate]
    </pre>

    However, if the user needs to be able to edit the NextFillDate, you must use code.

  7. #7
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if statement (XP)

    Well, duh, don't I feel silly. I hadn't changed anything in the record! Thanks for all your help and patience as usual! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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