By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,661 Members | 1,917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,661 IT Pros & Developers. It's quick & easy.

help finding bad record in query

P: n/a
Hi,

Using a soundex function that works with anything except an empty/null
string the following gives me an error part way through the results

select surname, soundex(surname)
from pd
where len(surname & "") > 0
and soundex(surname) = soundex("Smith")

The error is "data type mismatch in criteria expression"

The problem is, after this, all results turn to '#name?' and I can't find a
way of finding out what caused the error.

If I run the same query through a VBA module, iterating and debug.printing
the results I get the error 3071 'this expression is typed incorrectly, or
it is too complex to be evaluated...' and 'rst.movenext' is highlighted.

If I alter the query portion of the vba to a simple select surname query,
then move the soundex part to the iterative section (while not eof...) then
no error occurs..

Sorry if I haven't explained this greatly but I hope someone can shed light
on it!
Thanks,
Chris
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> select surname, soundex(surname)
from pd
where len(surname & "") > 0
and soundex(surname) = soundex("Smith")


If it works fine with anything but a null/empty string, then why not
do something like this:

SELECT surname, soundex(surname)
FROM pd
WHERE surname IS NOT NULL
AND soundex(surname)=soundex("Smith");

either that or create the query that removes the nulls first and then
base your Soundex query on that.
Nov 12 '05 #2

P: n/a
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
select surname, soundex(surname)
from pd
where len(surname & "") > 0
and soundex(surname) = soundex("Smith")


If it works fine with anything but a null/empty string, then why not
do something like this:

SELECT surname, soundex(surname)
FROM pd
WHERE surname IS NOT NULL
AND soundex(surname)=soundex("Smith");


Well that only checks if surname is not null, not that it's length is >0,
whereas the original query does both?

Thanks,
Chris
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.