Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date exceptions (2002)

    I'm trying to handle some date exceptions, but can't seem to get it to work correctly. I pasted the code below:

    ErrSl_Dt:
    Debug.Print "Sl_Dt: " & rsAdtrNew.Fields("Sl_Dt")
    Debug.Print "Year: " & Year(Mid(rsAdtrNew.Fields("Sl_Dt"), 5, 2))
    Debug.Print "Year str: " & Mid(rsAdtrNew.Fields("Sl_Dt"), 5, 2)
    Debug.Print "Month: " & Month(IIf(Mid(rsAdtrNew.Fields("Sl_Dt"), 1, 2) = "00", "01", _
    Mid(rsAdtrNew.Fields("Sl_Dt"), 1, 2)))
    Debug.Print "Month str: " & IIf(Mid(rsAdtrNew.Fields("Sl_Dt"), 1, 2) = "00", "01", _
    Mid(rsAdtrNew.Fields("Sl_Dt"), 1, 2))

    dtSl_Dt = DateSerial(Year(Mid(rsAdtrNew.Fields("Sl_Dt"), 5, 2)), _
    Month(IIf(Mid(rsAdtrNew.Fields("Sl_Dt"), 1, 2) = "00", "01", _
    Mid(rsAdtrNew.Fields("Sl_Dt"), 3, 2))) + 1, 0)
    Debug.Print "dtSl_dt: " & dtSl_Dt

    Resume Sl_Dt

    Debug output:
    Sl_Dt: 023198
    Year: 1900
    Year str: 98
    Month: 1
    Month str: 02
    dtSl_dt: 1/31/1900

    I receive the date as a string, I'd like to convert 2/31/98 to 2/28/98 when this type of exception occurs. I've read some posts here on how to calculate the last day of the month, but I'm missing something. Any help would be appreciated.

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

    Re: Date exceptions (2002)

    Since your code looks like the error handling part of a procedure or function, I assume you'll only get here if the date string is not valid. Does this only happen if the day number is too high for the month? Or can there be other errors, such as month=13? If it's only a day number beyond the end of the month, this should do it:

    Dim strDate As String
    Dim intMonth As Integer
    Dim intYear As Integer

    strDate = rsAdtrNew.Fields("Sl_Dt")
    intMonth = Val(Left(strDate, 2))
    intYear = Val(Right(strDate, 2))
    If intYear < 30 Then
    intYear = intYear + 2000
    Else
    intYear = intYear + 1900
    End If

    ' If you need the date value:
    dtSl_Dt = DateSerial(intYear, intMonth + 1, 0)
    ' If you need the converted string value:
    strDate = Format(dtSl_Dt, "mmddyy")

    The last day of a month is equivalent (in VBA) to the 0th day of the next month.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date exceptions (2002)

    Thanks Hans, that worked.

    The only invalid months I've received so far is "00", I used an IIf to change it to 01. Do you have any suggestion on month validation? I'm really not sure what type of invalid dates are going to be received, if you've got any suggestions on general date validation I'd be interested.

    I still have some questions related to my old code. Why does the DateSerial function seem to be able to handle two digit years and the Year function couldn't? And, why didn't the Month function work?

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

    Re: Date exceptions (2002)

    Your use of the Month and Year functions was incorrect. Day, Month and Year operate on a complete date, e.g. Month(#06/27/05#) and Month("27-Jun-05") both return 6. But you first extracted the month part of the date string by taking the 2 leftmost characters, resulting in "06", then applied the Month function to the "06". That makes no sense.

Posting Permissions

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