Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Question Unanswered: Date check question?

    Season Greetings,
    Simple question...

    Background: I have an event on the same date every year (like a birth date).

    I want to populate a field when the next date is coming with the correct year. How can I compare the re-occurring date (day/month) whether it's going to occur in the current year or has past and is due the next year without attaching a year to the re-occurring date?

    Example: If today's date is 21 Dec (which it is) and 5 May is my date, then it is past and my populated field would be 5 May 2007; or if the re-occurring date is 29 Dec, then the next date is 29 Dec 2006.

    Thanks in advance for you help

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Something like this should work, where OldDate is the control holding the date you want to update if necessary:

    Code:
    Private Sub Form_Current()
       If Now() > Me.OldDate.Value Then
          Me.OldDate.Value = DateAdd("yyyy", 1, Me.OldDate.Value)
       End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2005
    Posts
    6
    Not going to work...it get's more complicated.

    The field being populated can't be compared. Depending on who the individual is, may have a different date based on an input from another field. ie., If the input is (1) the date compared is (5 May) and is different to an input of (2) that would be (29 Dec). The date compared needs to be compared to real time vs an already populated date field (which can change).

    Also, upon entering in a new record the field being populated (the date) will be unknown until a selection has been made from the other input (1 or 2).

    Again, thanks for helping.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if thisdate > thatdate then
    mybox = thatdate
    else
    mybox = dateserial(year(thatdate)+1, month(thatdate), day(thatdate))
    endif

    it should also go immediate
    iif(thisdate > thatdate, thatdate, dateserial(year(thatdate)+1, month(thatdate), day(thatdate)))

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Oct 2005
    Posts
    6
    Maybe I am missing something...

    ...Everything I tried requires the complete date "day-mo-yr" to compare (which works great for this year)...What I am trying to do is compare is the "day-mo" only without the year to keep it flexible for years to come. Is there a wild character for "day-mo-wildcharacter" to compare with. I'm trying to avoid having to manually update an automated function every year with new dates.

    Hope this clears up my intentions...

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, so store the day and month as seperate number fields and follow izy's post using Now() for the compare date and a concocted date using the current year and the two number fields as the desired date to get the correct result.

    tc

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    smells like a Windoze regional settings issue.

    try tc's stuff
    or
    try messing with isdate(someTextBox) and ISO date format on each of the dates
    (which A astonishingly accepts)

    public function makeISOdate (someDate as variant) as string
    if isdate(somedate) then
    makeisodate = format(somedate, "yyyymmdd")
    else
    msgbox "enter dates in your reqional preferences setting " _
    & format(34567, "Short Date"), vbexclamation, "YOU are the problem"
    endif
    end function


    so the initial thing switches from
    if thisdate > thatdate then
    to
    if makeisodate(thisdate) > makeisodate(thatdate) then


    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Oct 2005
    Posts
    6

    Smile

    Bingo! Problem solved...Here is the results:

    Dim mybox
    Dim Date1
    Dim Date2
    Dim ReportDue As Date
    Dim DayMonth
    Dim CurrentYear

    CurrentYear = DatePart("yyyy", Now())

    Date1 = "7/15/"
    Date2 = "12/29/"

    If [Field] = 1 Then DayMonth = Date1
    If [Field] = 2 Then DayMonth = Date2

    ReportDue = ([DayMonth] & "" & [CurrentYear])

    If Now() < ReportDue Then
    mybox = ReportDue
    Else
    mybox = DateSerial(Year(ReportDue) + 1, Month(ReportDue), Day(ReportDue))
    End If

    Much Thanks & Happy Holidays!

Posting Permissions

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