Re: Data type mismatch in criteria expression. (Error 3464)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

cableguy47905 via AccessMonster.com wrote:

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

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: As a generale rule - Query or VBA?
    ... But I end up with loads of queries with long names like: ... Stored queries have a precompiled query plan. ... For a code jockey the SQL string is tempting. ...
    (comp.databases.ms-access)
  • Re: Updating only changed records
    ... What exactly are the queries doing? ... a query that will update matching records, and add records that don't match. ... Dim stDocName1 As String ... Dim Msgstr2 As String ...
    (microsoft.public.access.formscoding)
  • Re: Make Multiple Tables via Query
    ... Execute method with a select query. ... Select queries contain a SELECT statement and can return ... Dim strExcelFileName As String ...
    (microsoft.public.access.queries)
  • Re: What do I need?
    ... Dim strFirst As String, strSecond As String, strThird As String ... Set rst = New ADODB.Recordset ... who have not audited a site on the occasions of its last three audits queries ... i have the query but i only want to assign certain values to variables. ...
    (microsoft.public.access.queries)
  • Data type mismatch in criteria expression. (Error 3464)
    ... I keep getting the Error 3464 when I run a particular query. ... Public Function FirstNumber(ByVal StringIn As String) As String ... Dim strChar As String ... For lngChar = 1 To Len ...
    (microsoft.public.access.formscoding)