Problem with DatePart Function (1 Viewer)

[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

 

[Pat Hartman]

As you noticed the DatePart() function returns strings. However the standard date functions - Month(), Day(), and Year() return integers. It is always better to use the correct function to serve your purpose.

Your code is way too complicated. I'm afraid I didn't spend a lot of time trying to figure out exactly what you were trying to accomplish but you can just compare one date to another. You don't need to break it into pieces and do individual compares.

If Date() > ExpiryDate Then
    Msgbox "demo has expired", vbOKOnly
Else
    Msgbox "demo has " & Date() - ExpiryDate & " Days", vbOkOnly
End If

Date format is an external thing and doesn't have any effect on how a date is stored. Access uses a double precision number to hold date/time information, NOT a string.

 

[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.