dcsimg
Results 1 to 6 of 6

Thread: IF Statement that is True for dates within 3 months of the current date

  1. #1

    Thread Starter
    Member
    Join Date
    May 2018
    Posts
    41

    IF Statement that is True for dates within 3 months of the current date

    Couldn't think of a more exciting title!

    I have an app which is populating transaction history for a customer from a RecordSet into a flexgrid. The transaction history is a complete history back to the beginning of time, so it just trundles through the database and searches for transactions matching that customer and if so adds them to the flexgrid.

    The client requires also a cumulative total of the value of transactions for that customer for the past 3 months only.

    'Simple' I thought, all I need to do when a customer matches is add an IF to compare the transaction date with a date 3 months before the current date, then if the transaction date is newer it adds to the total, if not the IF condition will not be met and it won't be added to the total.

    I've ended up with this:

    Code:
    If rs!Date > Format(DateAdd("m", -3, Now()), "dd/mm/yyyy") Then Total = Total + rs!Value
    This doesn't work as expected, instead all the IF tests come back False and nothing is added to the total. Alternatively, if I reverse the operator and change it to be Less Than, then all of the IF tests are True and everything is added to the total including transactions older than 3 months.

    I'm sure I'm missing something blindingly obvious but I've been stuck on this for hours!

  2. #2
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,514

    Re: IF Statement that is True for dates within 3 months of the current date

    You're doing a string comparison on the dates, and your date formatting isn't string comparable in a way that would give results you would naturally expect. For example, "12/02/2010" would be greater than "01/01/2019" with your test, which is not what you are looking for.

    Is the datatype of the Date field in your database actually a string that is formatted in dd/mm/yyyy format? Or is it a Date or DateTime datatype?

    If the Date column is a Date datatype, get rid of your Format() call wrapped around DateAdd.

  3. #3

    Thread Starter
    Member
    Join Date
    May 2018
    Posts
    41

    Re: IF Statement that is True for dates within 3 months of the current date

    And there's the blindingly obvious thing I missed! The Date field in the Database is only a string. Changing it to Date/Time fixed the problem. Thanks

    However, the client already has the app in production across a number of sites. To make this work I would need to change the database setup for them on all of their production systems, or give them instructions on how to do it themselves when they normally expect an update to just be a recompile of the program that they can copy on top of the old one. I have tried leaving the database field as a String and then using CDate(rs!Date) to convert the string to a date before doing the comparison. That also seems to work fine. Are there any inherent problems in using that as a solution instead?

  4. #4
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,514

    Re: IF Statement that is True for dates within 3 months of the current date

    Is the data in the database always consistently stored as a string in the exact dd/mm/yyyy format? That is, are you sure there isn't a mix of formats in the database such as "Dec 12, 2019", "12-12-2019", "20191212", "12-DEC-19", etc... - it's always formatted like "12/12/2019"?

  5. #5

    Thread Starter
    Member
    Join Date
    May 2018
    Posts
    41

    Re: IF Statement that is True for dates within 3 months of the current date

    Quote Originally Posted by jpbro View Post
    Is the data in the database always consistently stored as a string in the exact dd/mm/yyyy format? That is, are you sure there isn't a mix of formats in the database such as "Dec 12, 2019", "12-12-2019", "20191212", "12-DEC-19", etc... - it's always formatted like "12/12/2019"?
    Always exactly dd/mm/yyyy - the Date field is populated by the program, not directly by the user.

  6. #6
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,514

    Re: IF Statement that is True for dates within 3 months of the current date

    Unfortunately it's not the invariant locale/culture date format (which is "MM/DD/YYYY"), so that makes things a bit trickier I think. CDate will give you different results in different locales for dates like 11/12/2020 because the date could be interpreted as Nov 12, 2020 or Dec 11, 2020. Something like this should work, but someone else may have a better solution:

    Code:
    Sub SomeSub()
       If DDMMYYYYToDate(rs!Date) > DateAdd("m", -3, Date) Then
         ' Do Something
       End If
    End Sub
    
    Public Function DDMMYYYYToDate(ByVal p_DDMMYYYYDateString As String) As Date
       p_DDMMYYYYDateString = Trim$(p_DDMMYYYYDateString)
       If Len(p_DDMMYYYYDateString) <> 0 Then Err.Raise 5, , "Invalid date format for date '" & p_DDMMYYYYDateString & "'. Must be DDMMYYYY"
    
       DDMMYYYYToDate = DateSerial(Right$(p_DDMMYYYYDateString, 4), _
                                   Mid$(p_DDMMYYYYDateString, 4, 2), _
                                   Left$(p_DDMMYYYYDateString, 2))
    End Function
    Last edited by jpbro; Jan 11th, 2020 at 10:39 PM.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width