Access SQL query and user defined function

Question

G'day,

I'm struggling with a problem in Access around a SQL query

If I use the following query:

SELECT DET_Data.Country, 0 as R_Status, 0 as X_Status, 0 as A_Status, 1 as C_Status, HP_QTRs.HQTR, HP_QTRs.SMonth, 2012 as RYear, 0 as RMonth, 1 as RQtr

FROM DET_Data, HP_QTRs

WHERE DET_Data.Request_Type="CE"

And (Month([DET_Data.Date_Completed])=HP_QTRs.NMonth)

**AND (HP_FYQ([DET_Data.Date_Completed]) = 20121)**AND ([DET_Data.Win] In ("C"));

I get the error "Data type mismatch in criteria expression"

However if I have the data stored in a field in the db and use the following query:

SELECT DET_Data.Country, 0 as R_Status, 0 as X_Status, 0 as A_Status, 1 as C_Status, HP_QTRs.HQTR, HP_QTRs.SMonth, 2012 as RYear, 0 as RMonth, 1 as RQtr

FROM DET_Data, HP_QTRs

WHERE DET_Data.Request_Type="CE"

And (Month([DET_Data.Date_Completed])=HP_QTRs.NMonth)

**AND ([DET_Data.Date_Completed_FYQ] = 20121)**AND ([DET_Data.Win] In ("C"));

It works as expected

The User defined function is:

Public Function HP_FYQ(ByRef TestDate As Date) As Long

' Pass the queried date

' Returns the financial year

    'Debug.Print "testDate is " & testDate

    HP_FYQ = ((Year(TestDate) + IIf(Month(TestDate) > 10, 1, 0)) * 10) + (Int((((Month(TestDate) + 1) Mod 12) / 3) + 1))

End If

End Function

I have set up a test form and the function returns the expected value

Everything works with internal functions, like "month" as used above, but it seems like using a UDF is a problem for some reason, or more likely the way I have done it.

Advise appreciated.

Thanks

Answer 1

The code for the HP_FYQ function contains a superfluous End If.

HP_FYQ expects a date as argument. If DET_Data.Date_Completed is Null (blank), it'll return an error, and you'll get the Type Mismatch error that you report.

Try changing the function as follows:

Public Function HP_FYQ(ByRef TestDate As Variant) As Long

' Pass the queried date

' Returns the financial year

    If IsDate(TestDate) Then

        HP_FYQ = ((Year(TestDate) + IIf(Month(TestDate) > 10, 1, 0)) * 10) + _

            (Int((((Month(TestDate) + 1) Mod 12) / 3) + 1))

    End If

End Function

Answer 2

If the first line of the function is

Public Function HP_FYQ(ByRef TestDate As Date) As Long

the function will only accept valid dates, and throw a fit if you pas a Null value. By changing Date to Variant, the function will accept any value including Null.

Answer 3

I'm afraid my crappy cut and paste tried to led you astray. The "If IsDate" test is what is supposed to be in  blank line, that is why the End If is there.

It does seem though setting TestDate as Variant rather than Date fixed the problem. I guess setting the Datatype as Date made it pass the IsDate test?

Thanks. This problem has been driving me up the wall for quite a while on and off.