Sort in an IIf statement

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!

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" wrote:

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.Bl dgEnclosure) ON
BldgRoof_Table.BldgRoof=Project_Table.BldgRoof) ON
BldgStructure_Table.BldgStructure=Project_Table.Bl dgStructure) ON
Employee_Table.EmployeeNumber=Project_Table.Employ eeNumber) 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" wrote:

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" wrote:

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!

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" wrote in message
...
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!

At the very end.


Vanderghast, Access MVP

"Huber57" wrote in message
...
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.Bl dgEnclosure) ON
BldgRoof_Table.BldgRoof=Project_Table.BldgRoof) ON
BldgStructure_Table.BldgStructure=Project_Table.Bl dgStructure) ON
Employee_Table.EmployeeNumber=Project_Table.Employ eeNumber) 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" wrote:

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" wrote:

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 wrote:
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(LLCNumber0,Format(LLCNumber,"0000"),"XX"),"SD ")

and sort on this field.

--
Marsh
MVP [MS Access]

Michel,

The last one worked like a charm.

Thanks much!


"Michel Walsh" wrote:

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" wrote in message
...
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!

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" wrote:

Huber57 wrote:
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(LLCNumber0,Format(LLCNumber,"0000"),"XX"),"SD ")

and sort on this field.

--
Marsh
MVP [MS Access]

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

Thanks everyone for your help.

"Huber57" wrote:

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" wrote:

Huber57 wrote:
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(LLCNumber0,Format(LLCNumber,"0000"),"XX"),"SD ")

and sort on this field.

--
Marsh
MVP [MS Access]

Huber57 wrote:

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

--
Marsh
MVP [MS Access]