Datatype mismatch in criteria expression

  • Thread starter Thread starter blobb
  • Start date Start date
B

blobb

I am trying to build a recordset off of an entry on a form that "sounds like"
or is similiar to the current entry. I have the below code that was working
well last time I used it but I am now getting the "Data type mismatch error
in criteria expression". Could someone help me identify why this is? I have
tried double quotes, single quotes, no quotes...

The "Soundex" is the function for the sounds like, [Crosswalk] is existing
table.


Dim rst As dao.Recordset
Dim strLast As String
Dim db As Database

Set db = CurrentDb()

'If on a new row.
If (Me.NewRecord = True) Then
'...check for duplicate Lastnames.
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for duplicate Lastnames.
Set rst = CurrentDb.OpenRecordset("SELECT PTid, Lastname,
FirstName, SSN FROM " & _
"Crosswalk WHERE Soundex([Lastname]) = '" &
Soundex(Me.LastName) & "'")

Thanks! blobb
 
It could be your soundex function is having a problem. Are there any records
in Crosswalk where LastName is null or a zero-length string? If so, do the
Soundex function handle those cases?

You might try testing with
IIF(Len(LastName & "") = 0,"zxvq",LastName)
to force a "legal" value to be passed to the Soundex function.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
The last name is a required field -- there are no blanks. Any other ideas?

John Spencer said:
It could be your soundex function is having a problem. Are there any records
in Crosswalk where LastName is null or a zero-length string? If so, do the
Soundex function handle those cases?

You might try testing with
IIF(Len(LastName & "") = 0,"zxvq",LastName)
to force a "legal" value to be passed to the Soundex function.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am trying to build a recordset off of an entry on a form that "sounds like"
or is similiar to the current entry. I have the below code that was working
well last time I used it but I am now getting the "Data type mismatch error
in criteria expression". Could someone help me identify why this is? I have
tried double quotes, single quotes, no quotes...

The "Soundex" is the function for the sounds like, [Crosswalk] is existing
table.


Dim rst As dao.Recordset
Dim strLast As String
Dim db As Database

Set db = CurrentDb()

'If on a new row.
If (Me.NewRecord = True) Then
'...check for duplicate Lastnames.
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for duplicate Lastnames.
Set rst = CurrentDb.OpenRecordset("SELECT PTid, Lastname,
FirstName, SSN FROM " & _
"Crosswalk WHERE Soundex([Lastname]) = '" &
Soundex(Me.LastName) & "'")

Thanks! blobb
Click to expand...
Click to expand...
 
How about me.LastName

I would check it with the following

IF Len(Trim(Me.LastName & vbNullString)) > 0 then
....

and not with IsNothing - is that a custom function or are you trying to do

If Not (Me.LastName is Nothing) Then ...
which will return true even if the VALUE property of Me.LastName is
Null or an empty string.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The last name is a required field -- there are no blanks. Any other ideas?

John Spencer said:
It could be your soundex function is having a problem. Are there any records
in Crosswalk where LastName is null or a zero-length string? If so, do the
Soundex function handle those cases?

You might try testing with
IIF(Len(LastName & "") = 0,"zxvq",LastName)
to force a "legal" value to be passed to the Soundex function.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am trying to build a recordset off of an entry on a form that "sounds like"
or is similiar to the current entry. I have the below code that was working
well last time I used it but I am now getting the "Data type mismatch error
in criteria expression". Could someone help me identify why this is? I have
tried double quotes, single quotes, no quotes...

The "Soundex" is the function for the sounds like, [Crosswalk] is existing
table.


Dim rst As dao.Recordset
Dim strLast As String
Dim db As Database

Set db = CurrentDb()

'If on a new row.
If (Me.NewRecord = True) Then
'...check for duplicate Lastnames.
If Not IsNothing(Me.LastName) Then
' Open a recordset to look for duplicate Lastnames.
Set rst = CurrentDb.OpenRecordset("SELECT PTid, Lastname,
FirstName, SSN FROM " & _
"Crosswalk WHERE Soundex([Lastname]) = '" &
Soundex(Me.LastName) & "'")

Thanks! blobb
Click to expand...
Click to expand...
Click to expand...
 
Back
Top