Data type mismatch in criteria expression. (Error 3464)

[cableguy47905 via AccessMonster.com]

The following is the function that I am using.
I keep getting the Error 3464 when I run a particular query.

This query is based on two other queries. One of those queries is using this
function on the field that left joined to a Union query. The Union query is
based on two other queries that are also using this same function on the same
field. If I run each of these "base" queries, I do not get the error. I
only get it when I run it on the first query.

Any Ideas as to why I might be getting this?
Thanks in advance,
Lee

Some of the values that I think might be giving it some troubles are:

<<66437 system unable to process when submit
#66625, system unable electronic enrollment
FW: p 67425 timing out/ system wants a pin not password


Public Function FirstNumber(ByVal StringIn As String) As String

Dim StringOut As String
Dim lngChar As Long
Dim strChar As String
Dim boolDigitFound As Boolean

Const strcDigits As String = "1234567890"

For lngChar = 1 To Len(StringIn)
strChar = Mid$(StringIn, lngChar, 1)
If InStr(1, strcDigits, strChar) <> 0 Then
boolDigitFound = True
StringOut = StringOut & strChar
Else
If boolDigitFound = True Then
Exit For
End If
End If
Next lngChar
FirstNumber = StringOut

End Function

 

[Marshall Barton]

The following is the function that I am using.
I keep getting the Error 3464 when I run a particular query.

This query is based on two other queries. One of those queries is using this
function on the field that left joined to a Union query. The Union query is
based on two other queries that are also using this same function on the same
field. If I run each of these "base" queries, I do not get the error. I
only get it when I run it on the first query.

Any Ideas as to why I might be getting this?
Thanks in advance,
Lee

Some of the values that I think might be giving it some troubles are:

<<66437 system unable to process when submit
#66625, system unable electronic enrollment
FW: p 67425 timing out/ system wants a pin not password


Public Function FirstNumber(ByVal StringIn As String) As String

Dim StringOut As String
Dim lngChar As Long
Dim strChar As String
Dim boolDigitFound As Boolean

Const strcDigits As String = "1234567890"

For lngChar = 1 To Len(StringIn)
strChar = Mid$(StringIn, lngChar, 1)
If InStr(1, strcDigits, strChar) <> 0 Then
boolDigitFound = True
StringOut = StringOut & strChar
Else
If boolDigitFound = True Then
Exit For
End If
End If
Next lngChar
FirstNumber = StringOut

End Function

You might get a data type mismatch if the value os the
function is compared to a number field.

Another possibility is that the result of the function is
being converted to a number type and then you compare it to
a Text field. This sometimes happens in compliccated query
arrangements, especially where the field can be Null in some
records.

You should be able to deak with it by explicitly xonverting
ot to the needed type:

numberfield = CLng(FirstNumber(textfield))

textfield = CStr(FirstNumber(textfield))

 

[cableguy47905 via AccessMonster.com]

Thanks, I will certainly try that. I think I have narrowed it down to the
Unmatched Query causing the problem. It is looking to match on fields that
are null. The following is the SQL for that. Is there some other way to do
an umatched query?

SELECT QRY_Pending.Project, QRY_Pending.Subject, QRY_Pending.Body,
QRY_Pending.From, QRY_Pending.Project_Status
FROM QRY_Pending LEFT JOIN QRY_HDRes_HDProbs ON QRY_Pending.Project =
QRY_HDRes_HDProbs.Project
WHERE (((QRY_HDRes_HDProbs.Project) Is Null))

TIA,
Lee

The following is the function that I am using.
I keep getting the Error 3464 when I run a particular query.

[quoted text clipped - 38 lines]

End Function

You might get a data type mismatch if the value os the
function is compared to a number field.

Another possibility is that the result of the function is
being converted to a number type and then you compare it to
a Text field. This sometimes happens in compliccated query
arrangements, especially where the field can be Null in some
records.

You should be able to deak with it by explicitly xonverting
ot to the needed type:

numberfield = CLng(FirstNumber(textfield))

textfield = CStr(FirstNumber(textfield))

 

[Marshall Barton]

On the suface, that looks good. The check for null is not
going to be the problem.

But the ON clause depends on where the two Project fields
are coming from. Try using the appropriate convert function
on one, the other or both.
--
Marsh
MVP [MS Access]

Thanks, I will certainly try that. I think I have narrowed it down to the
Unmatched Query causing the problem. It is looking to match on fields that
are null. The following is the SQL for that. Is there some other way to do
an umatched query?

SELECT QRY_Pending.Project, QRY_Pending.Subject, QRY_Pending.Body,
QRY_Pending.From, QRY_Pending.Project_Status
FROM QRY_Pending LEFT JOIN QRY_HDRes_HDProbs ON QRY_Pending.Project =
QRY_HDRes_HDProbs.Project
WHERE (((QRY_HDRes_HDProbs.Project) Is Null))

The following is the function that I am using.
I keep getting the Error 3464 when I run a particular query.

[quoted text clipped - 38 lines]

End Function

You might get a data type mismatch if the value os the
function is compared to a number field.

Another possibility is that the result of the function is
being converted to a number type and then you compare it to
a Text field. This sometimes happens in compliccated query
arrangements, especially where the field can be Null in some
records.

You should be able to deak with it by explicitly xonverting
ot to the needed type:

numberfield = CLng(FirstNumber(textfield))

textfield = CStr(FirstNumber(textfield))