Data Type Mismatch in custom function

[jayinthe813]

Hello, I am attempting to compare some dates in my queries. I have created a custom function I am attempting to use in a query. My function looks like this:

Public Function FPDA(ByVal EffDate As Date, DOB As Date, Mode As Integer, Age As Integer) As Date
'FUNCTION TO CALCULATE THE FIRST PREMIUM DUE AFTER X AGE

Select Case Mode
Case "1"
Do Until EffDate > DateAdd("yyyy", Age, [DOB]) And EffDate >= #4/1/2014# 'alternatively DateAdd("yyyy", 65 (or X age), DOB) if you want to use different age
EffDate = DateAdd("yyyy", 1, EffDate)
Loop

Case "2"
Do Until EffDate > DateAdd("yyyy", Age, [DOB]) And EffDate >= #4/1/2014#
EffDate = DateAdd("m", 6, EffDate)
Loop

Case "4"
Do Until EffDate > DateAdd("yyyy", Age, [DOB]) And EffDate >= #4/1/2014#
EffDate = DateAdd("q", 1, EffDate)
Loop

Case "12"
Do Until EffDate > DateAdd("yyyy", Age, [DOB]) And EffDate >= #4/1/2014#
EffDate = DateAdd("m", 1, EffDate)
Loop

End Select

'return
FPDA = EffDate

End Function

the SQL for my query looks like:

SELECT FPDA([EffectiveDate],[DOB],[Mode],60)
FROM tblAccounts INNER JOIN tblMasterData ON tblAccounts.AccountNum = tblMasterData.AccountNum
WHERE (((FPDA([EffectiveDate],[DOB],[Mode],75))<=DateAdd("d",365,Date())));

The field types are the following:
EffectiveDate - Date/Time
DOB - Date/Time
Mode - Number

When I run the query, I am getting Data Type Mismatch in criteria expression.
I think the problem is stemming from my criteria which is:

<=DateAdd("d",365,Date())

But I am not sure why, as FPDA returns a date type, and dateadd also returns a date type. What is my problem here?

 

[namliam]

Can mode or any of the values into the funcion be NULL by any chance?

 

[plog]

It could also be your join:

ON tblAccounts.AccountNum = tblMasterData.AccountNum

Is one a number and the other actually a text field?

Eliminate the WHERE clause and rerun the query. If it runs, you know its the WHERE clause. If it doesn't, its the JOIN. If it is the WHERE clause, eliminating it will let you see if you have any NULL\Error values returned from your custom function.

 

[jayinthe813]

Can mode or any of the values into the funcion be NULL by any chance?

Thank you! I am importing fixed-length text and apparently the text file contains two erroneous entries near the end of the file. These two records had nulls for all fields except the account number.

 

[REZ]

I've got the same issue.
I've got a custom function which returns a date, but when I use it in a query and try put a date in the criteria, I get data mismatch error.
The query works fine without the criteria, and when the query is used as a recordsource for a form I can see it is a date type, but when trying to filter it returns the same error.
Where have I gone wrong.
This is the function:

 Public Function autoExpiry(Tpe As String, Dte As Date) As Date

Dim interval As String

Dim Numb As Integer

Dim Expiry As Date


If Tpe = "SG" Then

interval = DLookup("SGvalue", "tblsystem")

Numb = DLookup("SGexpiry", "Tblsystem")

Else

interval = DLookup("FirstAvalue", "tblsystem")

Numb = DLookup("FirstAexpiry", "Tblsystem")

End If


If interval = "Years" Then

interval = "yyyy"

Else

Interal = "m"

End If

Expiry = DateAdd(interval, Numb, Dte)


autoExpiry = Expiry

End Function

 

[Minty]

You have a typo in your function

Interal = "m"

But suspect that might be an editing issue rather than you source of your problem.
When you remove the criteria, sort your results on the calculated expiry date (ascending and then decsending , and see what you get.
If you can't spot the error then post up the SQL of the query, verbatim, don't edit it.

 

[plog]

I've got a custom function which returns a date, but when I use it in a query and try put a date in the criteria, I get data mismatch error.
The query works fine without the criteria, and when the query is used as a recordsource for a form I can see it is a date type

Never trust your eyes with a computer. You can't tell the difference between a NULL, an empty string and a tab character. Just because your brain can see a date, doesn't mean it's a date data type to a computer. Two ways to make sure something is an actual date is to use DateValue and DateSerial:

MS Access: DateSerial Function

This MSAccess tutorial explains how to use the Access DateSerial function with syntax and examples. The Microsoft Access DateSerial function returns a date given a year, month, and day value.

www.techonthenet.com

https://www.techonthenet.com/access/functions/date/datevalue.php


Can you show us exactly how you are calling your autoExpiray() function from the query that is producing the error? Paste in here exactly what the query is.

 

[Gasman]

You have a typo in your function

Interal = "m"

But suspect that might be an editing issue rather than you source of your problem.
When you remove the criteria, sort your results on the calculated expiry date (ascending and then decsending , and see what you get.
If you can't spot the error then post up the SQL of the query, verbatim, don't edit it.

@REZ Why do you not have Option Explicit set?
That would show silly mistakes like that.

 

[REZ]

I don't know if you have your answer yet. Please respond to the questions posted by the others.

In the future, it is best for you if you start a new thread. Luckily, this old thread was very short but some get to be exceptionally long and no one wants to read through the entire old thread to get to your new question so many experts don't bother. So, if you think there is something relevant in the old thread, just post a link to it so people can look if they want to.

Noted. I will start a new thread next time.

I knew it must be something stupid I was doing.
The query was filtering for only one type, all the records returned by the query had valid dates.
But once I removed all the filters I could see plenty records with null/blanks.
I added an NZ function to the column, setting all nulls to 01/01/3000.
Seems to work fine now.

Thanks all for your help.