Problem with DatePart Function

[wlgzhang]

Hi All,
I have a function that checks the CQ evaluation expired date in my Access DB.
The Expired date using the short date format is DD/MM/YYYY
When I use Datepart function to extract the month from the Expired date, the function returns wrong Month.
Also, I have a very simple function to calculate the expired date, but the result is very funny. The Date I suppose to get is 27/11/2007, but the function returns 12:00:18 AM!
The problem is probably caused by the input format, but I do not know how to cope. Can anyone help!

The following is my code in the function of checking evaluation expired Date

If Not IsNull(rst.Fields(1).Value) Then
ExpDate = CDate(rst!ExpiryDay)
MsgBox ExpDate
Else
MsgBox "the field of ExpiryDay has not been recored in the table of NCAEvaluation. Please " _
& "check out before Planning exams.", , "Warring Info"
ValidExpiryDay = False
Exit Function
End If

CurMonth = CInt(DatePart("m", Date))
ExpMonth = CInt(DatePart("m", ExpDate))
CurYear = CDbl(DatePart("yyyy", Date))

ExpYear = CDbl(DatePart("yyyy", ExpDate))


If (CurYear - ExpYear) > 0 Then
MsgBox "wHAT up" & ExpYear
ValidExpiryDay = False
MsgBox "The Evaluation Date has Expiried", , "Expriry Date Info"
ElseIf (CurYear - ExpYear) = 0 Then
MsgBox "wHAT up Y0"
MsgBox CurMonth & " " & ExpMonth & CStr(CurMonth - ExpMonth)
If (CurMonth - ExpMonth) > 0 Then
MsgBox "wHAT up m0"
MsgBox "The Evaluation Date has Expiried", , "Expriry Date Info"
ValidExpiryDay = False

End If
Else
ValidExpiryDay = True
End If
The following code is in the calculating Expired Date
CurMonth = CLng(DatePart("m", Date))
CurDay = CLng(DatePart("d", ExpDate))
CurYear = CDbl(DatePart("yyyy", Date))

If CInt(Me.Examrequired) <= 5 Then
ExpYear = CurYear + 2
Me.ExpiryDay = CDate(CurMonth / CurDay / ExpYear)
Else

ExpYear = CurYear + 3

Me.ExpiryDay = CDate(CurMonth / CurDay / ExpYear)
MsgBox Me.ExpiryDay
End If

Note: Some of msgbox in the code is my debug msg.

Thank you!

 

[wlgzhang]

Hi all,
I have find solution for the calculating expired Date function. I use DateValue function to convert the calculated result and it works fine.

ExpYear = CurYear + 2
Me.ExpiryDay = DateValue(CStr(CurMonth) & "/" & CStr(CurDay) & "/" & CStr(ExpYear))

However, I have not yet to find out the solution for the other function.

Wei

 

[filo65]

Hi,

why instead of using DatePart(..) don't you use Day(), Month() Year() ? and Dateserial(...) to recombine.

Filippo

 

[wlgzhang]

problem with datepart function

Hi Pat & Filippo,
Both helps are solving my problem. Pat's suggestion is right. I can just compare with two dates and no need to break them.
Thanks again for the helps!
Wei.