Un-Natural Sort in Access Report

[bassman71]

Hi,
I've been given the task of keeping a record named "Not Provided" last in an ascending formated report. I thought about re-naming the records "zzNot Provided" in the underlying table and then trying some convoluted ActiveX dealy in which I would then camoflage the zz by chaging the font to white. Not sure if this would even work.

Any idears............Thanks...bassman

[JimmyTheHand]

:hi:

Look at Query1 in the attached database.

Cheers

Jimmy

[bassman71]

Jimmy,
My system does not recognize the file compression format. I cannot open the file.
Bassman

[JimmyTheHand]

That's too bad, it would have been so much easier if you can see it for yourslf.
Nevermind.
So, there is the table. Create a query that you want to display. Add a new field, which is an expression like this:

InStr(1;[Table1]![Name];"Not Provided";0)*-1 You surely know the InStr function, this expression returns
zero to everything that doesn't contain substring "Non Provided"
a negative number to everything that does contain substring "Non Provided"Put this new field to the first position, the make the query sorted by this value, before any else sorting takes place. In the end, hide the field, so that it isn't displayed.


EDIT:
Better yet, if you use

Sgn(InStr(1;[Table1]![Name];"Not Provided";0)*1) so that strings conaining "Not Provided" will not be forced into any order when ordering by this field. The previous expression gave -2 for those cases, where the field value was " Not Provided" (note the space).

EDIT2:
Oh, and most probably your US Access will have commas instead of semicolons...

Sgn(InStr(1,[Table1]![Name],"Not Provided",0)*1)
[Name] is the name of the field where the value may or may not be provided.

[bassman71]

Hey thanks so much Jimmy. It's funny, I use InStr(and many other functions) all the time in Excel, but never think they're applicable to Access.

Thanks again ...........

[Tom]

Going with Jimmy's code, you could use a numeric one in the first column except for "Not Provided" which would be a 2. Your first sort would be on the numeric data items just don't check the "show box"

[CBrine]

Another Alternative.

SortField: iif([Field Name]="Not Provided","zzzNot Provided",[Field Name])

Then sort on the new query field, but show the original field.

HTH
Cal

[stanl]

For my own edumacation... why not just an SQL query with a conditional IF in an ORDER BY... clause?

Stan

[JimmyTheHand]

For my own edumacation... why not just an SQL query with a conditional IF in an ORDER BY... clause?

Stan
Hi Stan

If you can solve the problem in your own way, that's great, and you are free to do it. I don't think there is a single best approach. I suggested using InStr for two reasons.

1.) Access query designer has a graphical interface for selecting tables, fields and expressions, and formulating filter conditions. Many users don't ever switch to SQL mode, unless by mistake, and as a result, they are not familiar with SQL. I'm not an SQL guru, either, so using VBA functions just comes easier to me, and I think to others as well.

2.) In my experience Access doesn't eat anything you want to feed it, even if it's a high class, and absolutely standard, SQL expression. I'm more or less familiar with Oracle SQL, and I remember some occasions when I struggled :banghead: to understand why Access wouldn't accept my SQL SELECT, even though syntax was correct. So I came to label Access as "different", and gave up understanding it. :dunno And don't use SQL in Access unless I must. All the more so because I've never met a Microsoft Reference Guide to the SQL language Access uses.

Speaking of which, if, by chance, anyone knows of such a reference guide, please, let me know : pray2:


Jimmy

Edit:
:doh: I made a mistake of a beginner ranking learner of English language. I wanted to say:
"In my experience Access doesn't eat everything you want to feed it, ..."

[stanl]

Speaking of which, if, by chance, anyone knows of such a reference guide, please, let me know : pray2:
Jimmy

http://www.bunkerhill.com/Mig2Ora.html (http://www.bunkerhill.com/Mig2Ora.html)

Is a start, but probably not up to date. It is obvious you cannot find an ORACLE substitute for Access's TRANSFORM...PIVOT w.out resort to a stored proc, but I think most standard SQL [SELECT ORDER BY GROUP BY HAVING] works across DB's. Stan

[JimmyTheHand]

(http://www.bunkerhill.com/Mig2Ora.html)but I think most standard SQL [SELECT ORDER BY GROUP BY HAVING] works across DB's. Stan

Yes, agreed. See my previous post updated.