Sort in an IIf statement

[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 Whittle]

At the bottom of the SQL statement try putting:

ORDER BY LLCNumber

 

[Huber57]

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!

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.

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!

 

[Michel Walsh]

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

 

[Michel Walsh]

At the very end.


Vanderghast, Access MVP

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!

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.

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!

 

[Marshall Barton]

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?

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.

 

[Huber57]

Michel,

The last one worked like a charm.

Thanks much!

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

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!

 

[Huber57]

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.

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?

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.

 

[Huber57]

Nevermind, Sorry. I had not formatted the number in the original expression
to Format([LLCNumber,"0000")

Thanks everyone for your help.

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.

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?

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.

 

[Marshall Barton]

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.

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).