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

Sorting Integers in a Query (Using Nz)

P: n/a
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 -

Dec 27 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 -
Dec 27 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.