Type Mismatch Error While Executing The Below Functions, Access 2010

[Baalu]

Hi Friends,

I am trying to calculate the difference between quarters, I wrote the below two functions, while executing I am getting Type Mismatch error. Please help me.

Public Function Calc_Quarter(ByVal dtDate As Date) As String
On Error GoTo Err_CQ

Dim CD As String
Dim dt As Date
Dim Qyear, Qmonth, Quarter As Integer

dt = dtDate
Qyear = Year(dt)
Qmonth = Month(dt)

If Qmonth = 1 Then
    Qyear = Qyear - 1
    Qmonth = 12
Else
    Qmonth = Qmonth - 1
End If

Select Case Qmonth
    Case 1, 2, 3
        Quarter = 1
    Case 4, 5, 6
        Quarter = 2
    Case 7, 8, 9
        Quarter = 3
    Case 10, 11, 12
        Quarter = 4
End Select

Calc_Quarter = "Q" & Quarter & " " & Qyear

Err_CQ:
If Err.Number <> 0 Then
'MsgBox "Error while calculating Quarter"
Calc_Quarter = "Error"
End If
End Function





Public Function EvidenceValid(ByVal dtDate As Date) As Boolean
On Error GoTo Err_EvidenceValid

Dim CurDt, GivenDt As Date
Dim CurDtQ, CurDtY, GvDtQ, GvDtY, QtDiff As Integer

CurDt = Date
GivenDt = dtDate

CurDtQ = CInt(Left(Calc_Quarter(CurDt), 1))
CurDtY = CInt(Mid(Calc_Quarter(CurDt), 2))
GvDtQ = CInt(Left(Calc_Quarter(GivenDt), 1))
GvDtY = CInt(Mid(Calc_Quarter(GivenDt), 2))
QtDiff = ((CurDtY * 4) + CurDtQ) - ((GvDtY * 4) + GvDtQ)

If QtDiff > 1 Then
EvidenceValid = False
Else
EvidenceValid = True
End If

Err_EvidenceValid:
If Err.Number <> 0 Then
EvidenceValid = False
Exit Function
End If
End Function

I am calling these two functions from a query and I am getting error while executing the "EvidenceValid" Function.

[orange]

A couple of thoughts:

This doesn't work as you think

Dim Qyear, Qmonth, Quarter As Integer

In Access you must explicitly dim variables.

Dim Qyear as Integer
Dim QMonth as Integer
Dim Quarter as Integer

or
Dim Qyear as Integer, QMonth as Integer...

Your approach results in qYear, qMonth as type Variant. Only quarter will be Integer.

You might want to research the DateDiff function.

[Baalu]

Hi,

Finally I found the actual problem and resolved it.
Problem is with Cint() which I used, I should actually use Val here.
Now the issue is resolved.

Thanks for your support,
Baalu