INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data type mismatch - vba function in query

Data type mismatch - vba function in query

(OP)
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

RE: Data type mismatch - vba function in query

(OP)
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!

RE: Data type mismatch - vba function in query

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.

CODE

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 

RE: Data type mismatch - vba function in query

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close