Sorting Integers in a Query (Using Nz)

[LoopyNZ]

Hi there,

I have an IIF expression that is returning a 0 (exact match), 1
(partial match), or 2 (no match) based on comparing text fields (see
below).

However, when I try to sort the results (either using an ORDER BY _or_
using the A-Z toolbar button on the datasheet), it doesn't work. It
sorts the 0s to the top, but the 1s and 2s are mixed up - the records
where one of the text fields is NULL show as 2s, but sort as 1s!

(If I wrap the entire expression in CInt(), it behaves the same way.)

If I remove the Nz() from around my text strings, the sorting works as
you'd expect (0s then 1s then 2s). But then the answers I get aren't
correct for matching purposes - e.g. Smith vs NULL comes up as a
"partial match", but for my purposes (i.e. being conservative) I want
it as a "no match".

Expression:
Surname_Match:
IIf(Person.Surname=New_Person.Surname,0,IIf(Person .Surname Like "*" &
Nz(New_Person.Surname,"0000") & "*" Or New_Person.Surname Like "*" &
Nz(Person.Surname,"0000") & "*",1,2))

Any idea how I can get this sort working without having to remove the
Nz()?

Thanks,
- LoopyNZ -

[LoopyNZ]

I've worked around the problem. I still don't completely understand why
the sorting would be so strange, but I've shifted the Nz() to outside
the core expression and also taken empty strings into account, and the
sorting now works.

New Expression:
Surname_Match: IIf(Nz([Person].[Surname],"")="" Or
Nz([New_Person].[Surname],"")="",2,IIf([Person].[Surname]=[New_Person].[Surname],0,IIf([Person].[Surname]
Like "*" & [New_Person].[Surname] & "*" Or [New_Person].[Surname] Like
"*" & [Person].[Surname] & "*",1,2)))
- LoopyNZ -

LoopyNZ wrote:

Hi there,

I have an IIF expression that is returning a 0 (exact match), 1
(partial match), or 2 (no match) based on comparing text fields (see
below).

However, when I try to sort the results (either using an ORDER BY _or_
using the A-Z toolbar button on the datasheet), it doesn't work. It
sorts the 0s to the top, but the 1s and 2s are mixed up - the records
where one of the text fields is NULL show as 2s, but sort as 1s!

(If I wrap the entire expression in CInt(), it behaves the same way.)

If I remove the Nz() from around my text strings, the sorting works as
you'd expect (0s then 1s then 2s). But then the answers I get aren't
correct for matching purposes - e.g. Smith vs NULL comes up as a
"partial match", but for my purposes (i.e. being conservative) I want
it as a "no match".

Expression:
Surname_Match:
IIf(Person.Surname=New_Person.Surname,0,IIf(Person .Surname Like "*" &
Nz(New_Person.Surname,"0000") & "*" Or New_Person.Surname Like "*" &
Nz(Person.Surname,"0000") & "*",1,2))

Any idea how I can get this sort working without having to remove the
Nz()?

Thanks,
- LoopyNZ -