help finding bad record in query

[C L Humphreys]

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

[Pieter Linden]

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

[C L Humphreys]

"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