Determine if any row items are different

  • Thread starter Thread starter Peter Bassett
  • Start date Start date
P

Peter Bassett

I have a request to find all columns in myTable in which any row members
are different. Any easy example is ("A", "A", "B") which can be found by
something like

iif(max(ColX) <> min(ColX), 1, 0)

but I didn't realize this approach fails if a row is empty, i.e. ("A", "A",
"")

This is because, for some reason defined by SQL, max="A" and min="A".

I therefore need another approach.

Thanks in advance for any help in a query.
Pete
 
I have a request to find all columns in myTable in which any row members
are different. Any easy example is ("A", "A", "B") which can be found by
something like

iif(max(ColX) <> min(ColX), 1, 0)

but I didn't realize this approach fails if a row is empty, i.e. ("A", "A",
"")

This is because, for some reason defined by SQL, max="A" and min="A".
Click to expand...

That's because NULLs have no value - they aren't less than anything
else, or more than anything else, or equal to anything else, or
unequal to anything else.

Try

Iif(Max(NZ([ColX]), "~") <> NZ(Min([ColX]), "~") Then

using some character that will never legitimately appear in ColX in
place of ~.
 
Thanks!

That did the trick!

Pete


Subject: Re: Determine if any row items are different
From: John Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
Newsgroups: microsoft.public.access.queries
Reply-To: (e-mail address removed)

I have a request to find all columns in myTable in which any row
members are different. Any easy example is ("A", "A", "B") which can
be found by something like

iif(max(ColX) <> min(ColX), 1, 0)

but I didn't realize this approach fails if a row is empty, i.e. ("A",
"A", "")

This is because, for some reason defined by SQL, max="A" and min="A".
Click to expand...

That's because NULLs have no value - they aren't less than anything
else, or more than anything else, or equal to anything else, or
unequal to anything else.

Try

Iif(Max(NZ([ColX]), "~") <> NZ(Min([ColX]), "~") Then

using some character that will never legitimately appear in ColX in
place of ~.
Click to expand...
 
Back
Top