difficult where clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to write a query to compare two name fields and return the "not ok"
from these examples...

J and J ... ok
J and JOHN ... ok
JERRY and JOHN ... not ok

No matter how I code this, I end up pulling 'ok' combinations. For
simplicity, I left out any scenarios that deal with NULLs.

Thanks
David
 
I think you want to use the LIKE comparison, but without more info
that's a HUGE guess.

Copy the SQL of your query, View->SQL View, and paste it here along
with table names and field names, and we'll get it fixed up.

Cheers,
Jason Lepack
 
Dear David:

It sounds like if both "first names" are one character long, they must match
exactly. If neither is one character long, they must match exactly.
Otherwise, only the first characther must match.

So, the logic is:

IIf(N1 = N2, True,
IIf(Len(N1) = 1 OR Len(N2) = 1, LEFT(N1, 1) = LEFT(N2, 1),
False))

Now, what do I mean by this?

N1 and N2 represent the two first name strings, stripped of leading or
trailing spaces (if necessary).

If they are itentical, then the value of the expression is true.

If not, then if either of the strings is one in length, the result is
determined by whether the first characters are equal

Otherwise, it is false.

I believe this is a brief way to say just what you intend. Try it, please.
How well does it work?

Tom Elison
Microsoft Access MVP
 
Fabulous! It works! Your logic identifies all the "ok" name 'combinations',
which I simply used as a subquery to find all those that are not ok.

SELECT x FROM y WHERE x NOT IN (tom's query)

I think I was too focused on pulling out the bad records when it was easier
to identify the good ones and then exclude them from the total. Also, I
hadn't considered using IIF.

Its a tad slow, but I think that is the nature of the string manipulation
and IIF usage. The two tables I'm currently comparing have over 6,000
records each. They are temporary tables so when I create them I might
calculate some stuff in advance and store the additional values in the table.

Thanks a million!
 
Back
Top