Question strange short date problem

[brad78]

Hi All,
I have a strange situation going on with one of my forms. I have tried everything I could think of to solve it, to no avail. On my form I have a calculated date field. the date field is calculated by a set review period and a last updated date which is typed in manually as a short date format.

ie. 1 month review period + 10/29/2008 = 11/29/2008

it seems to be working fine for most dates but when I enter in 03/01/2005 (march 1, 2005) as the last updated date, my calculation starts reading the date as 01/03/2005!

Somehow the month and day get switched! Please see my code below and let me know if you can spot the problem.

Private Sub expires_Enter()
    If (IsNull(Me.document_review_period.Value)) Or (IsNull(Me.doc_last_updated.Value)) Then
        MsgBox ("This field is calculated based on the content review period and the documents last updated date." & vbCrLf & "Please select a content review period and enter the documents last updated date to complete this field.")
    Else
 
        Dim review As String
        Dim last As String
        Dim myday As Integer
        Dim myMonth As Integer
        Dim myyear As Integer
        Dim diff As Integer
 
        review = Me.document_review_period
        last = Me.doc_last_updated
 
        myday = day(last)
        myMonth = month(last)
        myyear = year(last)
 
        If (review = "1 year") Then
            myyear = myyear + 1
            expires.Value = myMonth & "/" & myday & "/" & myyear
        End If
 
        If (review = "2 years") Then
            myyear = myyear + 2
            'MsgBox ("month " & myMonth & vbCrLf & "day" & myday & vbCrLf & "year" & myyear)
            expires.Value = myMonth & "/" & myday & "/" & myyear
        End If
 
        If (review = "1 month") Then
            myMonth = myMonth + 1
                If (myMonth > 12) Then
                    myMonth = 1
                    myyear = myyear + 1
                    expires.Value = myMonth & "/" & myday & "/" & myyear
                Else
                    expires.Value = myMonth & "/" & myday & "/" & myyear
                End If
        End If
 
        If (review = "3 months") Then
            myMonth = myMonth + 3
                If (myMonth > 12) Then
                    diff = myMonth - 12
                    myMonth = diff
                    myyear = myyear + 1
                    expires.Value = myMonth & "/" & myday & "/" & myyear
                Else
                    expires.Value = myMonth & "/" & myday & "/" & myyear
                End If
        End If
 
        If (review = "6 months") Then
            myMonth = myMonth + 6
                If (myMonth > 12) Then
                    diff = myMonth - 12
                    myMonth = diff
                    myyear = myyear + 1
                    'MsgBox ("month " & myMonth & vbCrLf & "day" & myday & vbCrLf & "year" & myyear)
                    expires.Value = myMonth & "/" & myday & "/" & myyear
                Else
                    expires.Value = myMonth & "/" & myday & "/" & myyear
                End If
        End If
    End If
End Sub

Any help is much appreciated!
Thanks

 

[stopher]

Normally the problem of day/month switching is experienced by people using computers set to non-US date. What is the region setting on your computer?

I'm not sure where your problem lies. However, I would suggest you use standard date functions to get your result rather than converting to a string.

try this code:

    If (IsNull(Me.document_review_period.Value)) Or (IsNull(Me.doc_last_updated.Value)) Then
        MsgBox ("This field is calculated based on the content review period and the documents last updated date." & vbCrLf & "Please select a content review period and enter the documents last updated date to complete this field.")
    Else
 
        Dim review As String
        Dim last As Date
         
        review = Me.document_review_period
        last = Me.doc_last_updated

        If InStr(1, review, "month") > 0 Then
            Me.expires = DateAdd("m", Val(review), last)
        ElseIf InStr(1, review, "year") > 0 Then
            Me.expires = DateAdd("yyyy", Val(review), last)
        End If
        
    End If

Chris

 

[brad78]

Ok, so I think I have figured out my problem. If I switch from dates to strings and use the code below to get values and do the math, everything seems fine.

        myday = Mid(last, 4, 2)
        myMonth = Left(last, 2)
        myyear = Right(last, 4)

Thanks Guys!

 

[stopher]

It's good to hear that you sorted it out. But you might want to consider my code in any case.

For one reason you should have no reason to dabble in strings. The date functions work very well at adding months and years.

The second thing is I've reduced your 45 lines of code (approx) down to 7. That kind of saving is going to help you when coding in future (not that my code is perfect of course).

Chris

 

[brad78]

Thanks Chris, Appreciate the help!