Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    25

    Unanswered: Help calculating a date

    Hello, I have posted a similar problem to this before, and you helped me work it out. However, my poor sleep deprived brain cannot make the leap to apply the solution to this one.

    I need to calculate a date in a form based on a participant's birthday. If they are 15yrs or over, then I need to display a field called [BronzeComplete] if they are younger than 15, I need to display a field showing the date of their 15th birthday.

    This is what I have so far, but it's not working the way I want it to...

    =IIf(("m",[DOB],Date())<180, (DateAdd("m",180,[DOB])), [BronzeComplete])

    I am just getting every one of them showing the date of their 15th birthday regardless of wether they are already 15 or not.

    What am I doing wrong??!!!

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Gecko!

    Look at "Demo15yrsA2000.mdb", open Form1 and try.
    Attached Files Attached Files

  3. #3
    Join Date
    May 2006
    Posts
    25

    Thank you and sorry...

    Hi MStef-ZG,

    I'm so sorry, I didn't explain myself very well yesterday (blame it on the sleep deprivation). Thank you for the form you sent, it works great, but unfortunately it's not what I need.

    I got my terms mixed up. What I am working with is a REPORT, not a form (doh!). I have a field on the report that needs to show the date of the participant's birthday if they are under 15, or the [BronzeComplete] date if they are already 15 or over.

    My apologies for giving you unnecessary work!

    Any suggestions (if you are still speaking to me) much appreciated.

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello Gecko!

    No problem, adapt this code (from form) in report (Detail, On Format event).

  5. #5
    Join Date
    May 2006
    Posts
    25

    Thanks so much!

    I don't know what it was, but something in your code just made something click in my brain and I managed to get it working.

    I realised that I had to take another factor into consideration as well. If a Participant comes into the program at the Silver level (as opposed to the Bronze level - the starting level), then the database needed to calculate the dates differently.

    Anyway, I have put the code in the Report Detail On Format event. This is the code I ended up with - it's probably a bit clumsy, but at least it works!

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    Dim Age As Long
    Age = DateDiff("yyyy", Me.DOB, Now)
    
    'Calculates the start date for the Participant's Silver level
    
    If Me.StartDukeLevel = "Silver" Then
        Me.txtStartDate = Me.StartDate
    ElseIf Age < 15 Then
        Me.txtStartDate = DateAdd("m", 180, Me.DOB)
    Else
        Me.txtStartDate = Me.BronzeComplete
    End If
    
    'Calculates the minimum completion date for the Participant's Silver level
    
    If Me.StartDukeLevel = "Silver" Then
        Me.txtCompleteDate = DateAdd("m", 12, Me.StartDate)
    ElseIf Age >= 15 Then
        Me.txtCompleteDate = DateAdd("m", 6, Me.txtStartDate)
    Else
        Me.txtCompleteDate = DateAdd("m", 186, Me.DOB)
    End If
    
    End Sub
    Thanks again for your help.

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    My congratulation Gecko!

Posting Permissions

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