Data type mismatch - vba function in query 1

[Statistician]

Wonder if anyone is able to help please (I have scanned through some threads but cannot find a solution).

I have a simple query (using Access 2010) where I've used a vba function to use a date field in the query and return a period value (integer).
When I try to enter something like >1 in the criteria row of the query (against this calculated field), I get an error message stating "Data type mismatch in criteria expression".

So, basically, all I want to be able to do is enter some criteria based on what periods I am interested in to return those records but am unable to.
Any advice/help is appreciated.

The function I am using in the query field is as follows:

Public Function GetPeriod(sentdate As Date) As Integer

Select Case sentdate
Case #1/1/2015# To #1/31/2015#
GetPeriod = 1
Case #1/1/2015# To #2/28/2015#
GetPeriod = 2
Case Else
GetPeriod = 0
End Select

End Function

 

[Statistician]

Okay, I managed to solve this one using some similar concepts in other threads.
If anyone is interested, I've listed my solution below.

The problem was that the query I was using to pull the "input" data for my vba function was making reference to a table where there were some null values in the dates field being used.
I didn't originally think this was a problem because my query had a "Is Not Null" criteria which I presumed was filtering all null dates out, but when using a VBA function in a query field, the expression used (for some strange reason) needs an additional null statement.

Originally I had in my query, a field called, FieldX : GetPeriod([DateFieldFromQuery])

I simply replaced this with the following:

FieldX : GetPeriod(IIf(IsNull([DateFieldFromQuery]),0,[DateFieldFromQuery]))

I was then able to place the criteria I wanted, for example >1.

Hope this helps someone...took me many hours to discover this!

 

[MajP]

I should probably make a FAQ on this subject. But every function I build that will be used in a query uses a variant for the argument and then I check to make sure it is not null. Then determine what to do if it is null.

Public Function GetPeriod(sentdate As Variant) As Integer

'ALWAYS make the parameter a variant to handle a null
'The return type is probably also a variant to return null if null is passed
'But in your case you want a 0 to be returned

'ALWAYS Check to make sure a null was not passed. Use something like 'not isnull" or isdate

If isDate(sentdate)
  Select Case sentdate
  Case #1/1/2015# To #1/31/2015#
    GetPeriod = 1
  Case #1/1/2015# To #2/28/2015#
    GetPeriod = 2
  ' in your example the case else is actually not needed because if 
  ' case 1 and 2 are not met your function will return 0 
  '  Case Else
  '  GetPeriod = 0
  End Select
end if
End Function

 

[Statistician]

Thanks for replying MajP. That's a great idea using a variant. I'll update my code accordingly. Cheers!