Error when calling function from query

[Robert Grazia]

Hi All,

I have recently inherited an application and working to fix some broken
parts. One of these things is a query that pulls data in from a function.

Here is the function:

Code
--------------------------------------------------------------------------------

Hi All,

I have recently inherited an application and working to fix some broken
parts. One of these things is a query that pulls data in from a function.

Here is the function:

Code
--------------------------------------------------------------------------------

Public Function BuildRenewalServices(intRUID As Integer, intLicenseNumber As
Integer, dtmRenewalDate As Date) As String Dim strServices As String
Dim rst As ADODB.Recordset Dim strSQL As String Set rst = New
ADODB.Recordset strSQL = "SELECT * FROM
qry_LTR_MergeLetters_SelectSvcs WHERE " & _
"(((qry_LTR_MergeLetters_SelectSvcs.RUID)=" & intRUID & ") AND
((qry_LTR_MergeLetters_SelectSvcs.dtm_RenewalReceived)=#" & _
dtmRenewalDate & "#) AND ((qry_LTR_MergeLetters_SelectSvcs.LicenseNumber)= "
& intLicenseNumber & "))" strServices = "" rst.Open strSQL,
CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic With
rst Do Until .EOF If strServices = "" Then
strServices = rst.Fields(2) Else strServices =
strServices & ", " & rst.Fields(2) End If .MoveNext
Loop End With BuildRenewalServices = strServices End Functio
--------------------------------------------------------------------------------


The query itself has the following fields (relevant ones only):

RUID
LicenseNumber
dtm_RenewalReceived
:::Other Info:::

Another field in this query calls the above function like so:

ListServices: BuildRenewalServices([RUID], [LicenseNumber],
[dtm_RenewalReceived])

Ok, so that's the process in a nutshell.

Now here's my problem... whenever this query is run, I receive the "Data
type mismatch in criteria expression" error.

I have no idea why this is occuring. I have placed "Debug.Print strSQL" and
"Debug.Print strServices" in my code and everything turns up clean in the
immediate window.

Any ideas regarding this problem?

Robert

--------------------------------------------------------------------------------


The query itself has the following fields (relevant ones only):

RUID
LicenseNumber
dtm_RenewalReceived
:::Other Info:::

Another field in this query calls the above function like so:

ListServices: BuildRenewalServices([RUID], [LicenseNumber],
[dtm_RenewalReceived])

Ok, so that's the process in a nutshell.

Now here's my problem... whenever this query is run, I receive the "Data
type mismatch in criteria expression" error.

I have no idea why this is occuring. I have placed "Debug.Print strSQL" and
"Debug.Print strServices" in my code and everything turns up clean in the
immediate window.

Any ideas regarding this problem?

Robert

 

[Ken Snell MVP]

Most likely cause is that one or more of your fields contains a NULL value.
Only Variant data types can hold a NULL value in VBA code, and all of the
arguments in your function call are not Variant data types. So you'll get a
data type mismatch if you try to put a NULL in an Integer variable, or into
a Date variable.

Wrap the fields in the function call with the Nz function so that you
replace a NULL value with some other value (e.g., an empty string for a
string, 0 for an integer, etc.).

Or change the data types in the function's argument list to be Variant data
types. If you change to Variant data types, the function will need to be
revised so that you test for a NULL value in the variables before you start
doing any calculations/manipulations of the values you're passing into the
function.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi All,

I have recently inherited an application and working to fix some broken
parts. One of these things is a query that pulls data in from a function.

Here is the function:

Code:
--------------------------------------------------------------------------------

Hi All,

I have recently inherited an application and working to fix some broken
parts. One of these things is a query that pulls data in from a function.

Here is the function:

Code:
--------------------------------------------------------------------------------

Public Function BuildRenewalServices(intRUID As Integer, intLicenseNumber
As
Integer, dtmRenewalDate As Date) As String Dim strServices As String
Dim rst As ADODB.Recordset Dim strSQL As String Set rst = New
ADODB.Recordset strSQL = "SELECT * FROM
qry_LTR_MergeLetters_SelectSvcs WHERE " & _
"(((qry_LTR_MergeLetters_SelectSvcs.RUID)=" & intRUID & ") AND
((qry_LTR_MergeLetters_SelectSvcs.dtm_RenewalReceived)=#" & _
dtmRenewalDate & "#) AND ((qry_LTR_MergeLetters_SelectSvcs.LicenseNumber)=
"
& intLicenseNumber & "))" strServices = "" rst.Open strSQL,
CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic With
rst Do Until .EOF If strServices = "" Then
strServices = rst.Fields(2) Else strServices =
strServices & ", " & rst.Fields(2) End If .MoveNext
Loop End With BuildRenewalServices = strServices End
Function
--------------------------------------------------------------------------------


The query itself has the following fields (relevant ones only):

RUID
LicenseNumber
dtm_RenewalReceived
:::Other Info:::

Another field in this query calls the above function like so:

ListServices: BuildRenewalServices([RUID], [LicenseNumber],
[dtm_RenewalReceived])

Ok, so that's the process in a nutshell.

Now here's my problem... whenever this query is run, I receive the "Data
type mismatch in criteria expression" error.

I have no idea why this is occuring. I have placed "Debug.Print strSQL"
and
"Debug.Print strServices" in my code and everything turns up clean in the
immediate window.

Any ideas regarding this problem?

Robert

--------------------------------------------------------------------------------


The query itself has the following fields (relevant ones only):

RUID
LicenseNumber
dtm_RenewalReceived
:::Other Info:::

Another field in this query calls the above function like so:

ListServices: BuildRenewalServices([RUID], [LicenseNumber],
[dtm_RenewalReceived])

Ok, so that's the process in a nutshell.

Now here's my problem... whenever this query is run, I receive the "Data
type mismatch in criteria expression" error.

I have no idea why this is occuring. I have placed "Debug.Print strSQL"
and
"Debug.Print strServices" in my code and everything turns up clean in the
immediate window.

Any ideas regarding this problem?

Robert

 

[Jerry Whittle]

Heaven knows that I'm not a coder; however make sure that RUID and
LicenseNumber are integers at the table plus dtm_RenewalReceived is a
Date/Time data type. As they are pulled from a query, make sure that
something isn't changing their data types like a format function or some
other data conversion function.

Also nulls might be a problem but I would expect a different error message.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi All,

I have recently inherited an application and working to fix some broken
parts. One of these things is a query that pulls data in from a function.

Here is the function:

Code:
--------------------------------------------------------------------------------

Hi All,

I have recently inherited an application and working to fix some broken
parts. One of these things is a query that pulls data in from a function.

Here is the function:

Code:
--------------------------------------------------------------------------------

Public Function BuildRenewalServices(intRUID As Integer, intLicenseNumber As
Integer, dtmRenewalDate As Date) As String Dim strServices As String
Dim rst As ADODB.Recordset Dim strSQL As String Set rst = New
ADODB.Recordset strSQL = "SELECT * FROM
qry_LTR_MergeLetters_SelectSvcs WHERE " & _
"(((qry_LTR_MergeLetters_SelectSvcs.RUID)=" & intRUID & ") AND
((qry_LTR_MergeLetters_SelectSvcs.dtm_RenewalReceived)=#" & _
dtmRenewalDate & "#) AND ((qry_LTR_MergeLetters_SelectSvcs.LicenseNumber)= "
& intLicenseNumber & "))" strServices = "" rst.Open strSQL,
CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic With
rst Do Until .EOF If strServices = "" Then
strServices = rst.Fields(2) Else strServices =
strServices & ", " & rst.Fields(2) End If .MoveNext
Loop End With BuildRenewalServices = strServices End Function
--------------------------------------------------------------------------------


The query itself has the following fields (relevant ones only):

RUID
LicenseNumber
dtm_RenewalReceived
:::Other Info:::

Another field in this query calls the above function like so:

ListServices: BuildRenewalServices([RUID], [LicenseNumber],
[dtm_RenewalReceived])

Ok, so that's the process in a nutshell.

Now here's my problem... whenever this query is run, I receive the "Data
type mismatch in criteria expression" error.

I have no idea why this is occuring. I have placed "Debug.Print strSQL" and
"Debug.Print strServices" in my code and everything turns up clean in the
immediate window.

Any ideas regarding this problem?

Robert

--------------------------------------------------------------------------------


The query itself has the following fields (relevant ones only):

RUID
LicenseNumber
dtm_RenewalReceived
:::Other Info:::

Another field in this query calls the above function like so:

ListServices: BuildRenewalServices([RUID], [LicenseNumber],
[dtm_RenewalReceived])

Ok, so that's the process in a nutshell.

Now here's my problem... whenever this query is run, I receive the "Data
type mismatch in criteria expression" error.

I have no idea why this is occuring. I have placed "Debug.Print strSQL" and
"Debug.Print strServices" in my code and everything turns up clean in the
immediate window.

Any ideas regarding this problem?

Robert