If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Sort in an IIf statement
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! |
#3
|
|||
|
|||
Sort in an IIf statement
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! |
#4
|
|||
|
|||
Sort in an IIf statement
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! |
#5
|
|||
|
|||
Sort in an IIf statement
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! |
#6
|
|||
|
|||
Sort in an IIf statement
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] |
#7
|
|||
|
|||
Sort in an IIf statement
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! |
#8
|
|||
|
|||
Sort in an IIf statement
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] |
#9
|
|||
|
|||
Sort in an IIf statement
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] |
#10
|
|||
|
|||
Sort in an IIf statement
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] |
Thread Tools | |
Display Modes | |
|
|