Sort in an IIf statement

  • Thread starter Thread starter Huber57
  • Start date Start date
H

Huber57

To all:

I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?

Thanks in advance for your help!
 
Jerry,

Here is my entire SQL statement for the entire query.

SELECT Project_Table.ProjectName, (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD")) AS Owner, [EmpFirstName] & " " &
[EmpLastName] AS [Development Mgr], Format([SiteSize],"#.00") AS Expr8,
Format([sqft],"#,###") AS Expr1,
IIf([ConstructionCost]>0,Format([ConstructionCost],"$#,##0;-$#,##0"),"TBD")
AS Expr2,
IIf([ConstructionCost]/[Sqft]>0,Format([ConstructionCost]/[Sqft],"Currency"),"TBD")
AS Expr4, BldgStructure_Table.*, BldgEnclosure_Table.*, BldgRoof_Table.*,
IIf([ConstStartDate]>0,Format([ConstStartDate],"mm/dd/yy"),"TBD") AS Expr5,
IIf([MHDate]>0,Format([MHDate],"mm/dd/yy"),"N/A") AS Expr3,
IIf([CertOccDate]>0,Format([CertOccDate],"mm/dd/yy"),"TBD") AS Expr6,
IIf([RentCommenceDate]>0,Format([RentCommenceDate],"mm/dd/yy"),"TBD") AS Expr7
FROM LLC_Table INNER JOIN (Employee_Table INNER JOIN (BldgStructure_Table
INNER JOIN (BldgRoof_Table INNER JOIN (BldgEnclosure_Table INNER JOIN
Project_Table ON
BldgEnclosure_Table.BldgEnclosure=Project_Table.BldgEnclosure) ON
BldgRoof_Table.BldgRoof=Project_Table.BldgRoof) ON
BldgStructure_Table.BldgStructure=Project_Table.BldgStructure) ON
Employee_Table.EmployeeNumber=Project_Table.EmployeeNumber) ON
LLC_Table.TaxID=Project_Table.TaxID;

So, do I enter the "ORDER BY LLCNumber" after the expression in question, or
at the very end?

Thanks!

Jerry Whittle said:
At the bottom of the SQL statement try putting:

ORDER BY LLCNumber
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Huber57 said:
To all:

I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?

Thanks in advance for your help!
Click to expand...
Click to expand...
 
The ORDER BY clause (as seen in SQL view) can look like:

.... ORDER BY LLCNumber

or

.... ORDER BY iif(LLCName="Smith Properties", -1, 0), LLCNumber

or


.... ORDER BY iif(LLCNmae="Smith Properties", -1, 0), iif(LLCNumber > 0, -1,
0), LLCNumber




Hoping it may help,
Vanderghast, Access MVP
 
At the very end.


Vanderghast, Access MVP

Huber57 said:
Jerry,

Here is my entire SQL statement for the entire query.

SELECT Project_Table.ProjectName, (IIf([LLCName]="Smith Properties","SP#"
&
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD")) AS Owner, [EmpFirstName] & "
" &
[EmpLastName] AS [Development Mgr], Format([SiteSize],"#.00") AS Expr8,
Format([sqft],"#,###") AS Expr1,
IIf([ConstructionCost]>0,Format([ConstructionCost],"$#,##0;-$#,##0"),"TBD")
AS Expr2,
IIf([ConstructionCost]/[Sqft]>0,Format([ConstructionCost]/[Sqft],"Currency"),"TBD")
AS Expr4, BldgStructure_Table.*, BldgEnclosure_Table.*, BldgRoof_Table.*,
IIf([ConstStartDate]>0,Format([ConstStartDate],"mm/dd/yy"),"TBD") AS
Expr5,
IIf([MHDate]>0,Format([MHDate],"mm/dd/yy"),"N/A") AS Expr3,
IIf([CertOccDate]>0,Format([CertOccDate],"mm/dd/yy"),"TBD") AS Expr6,
IIf([RentCommenceDate]>0,Format([RentCommenceDate],"mm/dd/yy"),"TBD") AS
Expr7
FROM LLC_Table INNER JOIN (Employee_Table INNER JOIN (BldgStructure_Table
INNER JOIN (BldgRoof_Table INNER JOIN (BldgEnclosure_Table INNER JOIN
Project_Table ON
BldgEnclosure_Table.BldgEnclosure=Project_Table.BldgEnclosure) ON
BldgRoof_Table.BldgRoof=Project_Table.BldgRoof) ON
BldgStructure_Table.BldgStructure=Project_Table.BldgStructure) ON
Employee_Table.EmployeeNumber=Project_Table.EmployeeNumber) ON
LLC_Table.TaxID=Project_Table.TaxID;

So, do I enter the "ORDER BY LLCNumber" after the expression in question,
or
at the very end?

Thanks!

Jerry Whittle said:
At the bottom of the SQL statement try putting:

ORDER BY LLCNumber
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Huber57 said:
To all:

I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as
it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Huber57 said:
I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?
Click to expand...


Just add the LLCNumber field to your query and uncheck the
Show box and set its Sort to Ascending.

If you need to sort by LLCName field too, then the above
will not be adequate. Instead, add a new calculated field:

Owner: IIf(LLCName="Smith Properties","SP#" &
IIf(LLCNumber>0,Format(LLCNumber,"0000"),"XX"),"SD")

and sort on this field.
 
Michel,

The last one worked like a charm.

Thanks much!


Michel Walsh said:
The ORDER BY clause (as seen in SQL view) can look like:

.... ORDER BY LLCNumber

or

.... ORDER BY iif(LLCName="Smith Properties", -1, 0), LLCNumber

or


.... ORDER BY iif(LLCNmae="Smith Properties", -1, 0), iif(LLCNumber > 0, -1,
0), LLCNumber




Hoping it may help,
Vanderghast, Access MVP



Huber57 said:
To all:

I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?

Thanks in advance for your help!
Click to expand...
Click to expand...
 
That works great in my query. Thanks for that.

But, when the report it is bound to runs, it still show items out of order.

Marshall Barton said:
Huber57 said:
I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?
Click to expand...


Just add the LLCNumber field to your query and uncheck the
Show box and set its Sort to Ascending.

If you need to sort by LLCName field too, then the above
will not be adequate. Instead, add a new calculated field:

Owner: IIf(LLCName="Smith Properties","SP#" &
IIf(LLCNumber>0,Format(LLCNumber,"0000"),"XX"),"SD")

and sort on this field.
Click to expand...
 
Nevermind, Sorry. I had not formatted the number in the original expression
to Format([LLCNumber,"0000")

Thanks everyone for your help.

Huber57 said:
That works great in my query. Thanks for that.

But, when the report it is bound to runs, it still show items out of order.

Marshall Barton said:
Huber57 said:
I am using Access 2003.

I have a IIf statement in a query that looks like this:

Owner: (IIf([LLCName]="Smith Properties","SP#" &
(IIf([LLCNumber]>0,[LLCNumber],"XX")),"SD"))

The LLCNumber field is stored as a number.
The LLCName is stored as text.

It returns things like:
SP#101
SP#89
SP#45
SD
SP#XX
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.

I would like to sort these by the LLCNumber. Is that possible?
Click to expand...


Just add the LLCNumber field to your query and uncheck the
Show box and set its Sort to Ascending.

If you need to sort by LLCName field too, then the above
will not be adequate. Instead, add a new calculated field:

Owner: IIf(LLCName="Smith Properties","SP#" &
IIf(LLCNumber>0,Format(LLCNumber,"0000"),"XX"),"SD")

and sort on this field.
Click to expand...
Click to expand...
 
Huber57 said:
That works great in my query. Thanks for that.

But, when the report it is bound to runs, it still show items out of order.
Click to expand...


Not so fast. If you are using the query as the record
source for a report, then the query's sorting will be
irrelevant (and a waste of time) in all but very simple
reports. You should use the expression (or the calculated
field) in the report's Sorting and Grouping (View menu).
 
Back
Top