sorts query while including <all> at top of list

  • Thread starter Thread starter Mark Kubicki
  • Start date Start date
M

Mark Kubicki

I have a combobox whose data source is this query, which sorts the records
correctly:

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
;

however, when I add the following (to include the <all> word at the top of
the combolist...) it no longer sorts
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

Anysuggestions about how this couldbe accomplished?

many thanks inadvance,
Mark
 
The ORDER BY clause must always be at the end of Union queries. However, the
ORDER BY clause will have to be modified slightly, since it now applies to
the UNION query, not to table FigureTypesNoProject:

SELECT FixtureTypesNoProject.Type
FROM FixtureTypesNoProject
UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1),
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0"))
 
i made the change, but then got a message that my Order By clause included
field(s) that were not selected by the query

i then changed all of the [Type] to be FixtureTypesNoProject.Type , but
still got the same message (see below)

i can't find any that are not (but then i'm not always good at this stuff...
any additional thoughts?


SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject UNION Select "<
ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr( FixtureTypesNoProject.Type,"-")=0,Len(
FixtureTypesNoProject.Type),InStr( FixtureTypesNoProject.Type,"-")-1),
IIf(InStr(type,"EM")=0, FixtureTypesNoProject.Type,Replace(
FixtureTypesNoProject.Type,"EM","0"));


----------------------------------------------------------------------------------------------------------------------------------------------------
Douglas J. Steele said:
The ORDER BY clause must always be at the end of Union queries. However,
the ORDER BY clause will have to be modified slightly, since it now
applies to the UNION query, not to table FigureTypesNoProject:

SELECT FixtureTypesNoProject.Type
FROM FixtureTypesNoProject
UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1),
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
I have a combobox whose data source is this query, which sorts the records
correctly:

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
;

however, when I add the following (to include the <all> word at the top
of the combolist...) it no longer sorts
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

Anysuggestions about how this couldbe accomplished?

many thanks inadvance,
Mark
Click to expand...
Click to expand...
 
As I said, because the ORDER BY clause applies to the UNION query, not to
table FixtureTypesNoProject, you cannot put the table name into the ORDER BY
clause.

Are you saying that the SQL statement I suggested gave you the error
message?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
i made the change, but then got a message that my Order By clause included
field(s) that were not selected by the query

i then changed all of the [Type] to be FixtureTypesNoProject.Type , but
still got the same message (see below)

i can't find any that are not (but then i'm not always good at this
stuff...
any additional thoughts?


SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject UNION Select
"< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr( FixtureTypesNoProject.Type,"-")=0,Len(
FixtureTypesNoProject.Type),InStr( FixtureTypesNoProject.Type,"-")-1),
IIf(InStr(type,"EM")=0, FixtureTypesNoProject.Type,Replace(
FixtureTypesNoProject.Type,"EM","0"));


----------------------------------------------------------------------------------------------------------------------------------------------------
Douglas J. Steele said:
The ORDER BY clause must always be at the end of Union queries. However,
the ORDER BY clause will have to be modified slightly, since it now
applies to the UNION query, not to table FigureTypesNoProject:

SELECT FixtureTypesNoProject.Type
FROM FixtureTypesNoProject
UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1),
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
I have a combobox whose data source is this query, which sorts the
records correctly:

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
;

however, when I add the following (to include the <all> word at the top
of the combolist...) it no longer sorts
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

Anysuggestions about how this couldbe accomplished?

many thanks inadvance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
so I changed it back to the way you had it wwritten (I completely
misunderstood what you had meant -my fault, apologies..)
but , i'm still getting the same error...


Douglas J. Steele said:
As I said, because the ORDER BY clause applies to the UNION query, not to
table FixtureTypesNoProject, you cannot put the table name into the ORDER
BY clause.

Are you saying that the SQL statement I suggested gave you the error
message?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
i made the change, but then got a message that my Order By clause included
field(s) that were not selected by the query

i then changed all of the [Type] to be FixtureTypesNoProject.Type , but
still got the same message (see below)

i can't find any that are not (but then i'm not always good at this
stuff...
any additional thoughts?


SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject UNION Select
"< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr( FixtureTypesNoProject.Type,"-")=0,Len(
FixtureTypesNoProject.Type),InStr( FixtureTypesNoProject.Type,"-")-1),
IIf(InStr(type,"EM")=0, FixtureTypesNoProject.Type,Replace(
FixtureTypesNoProject.Type,"EM","0"));


----------------------------------------------------------------------------------------------------------------------------------------------------
Douglas J. Steele said:
The ORDER BY clause must always be at the end of Union queries. However,
the ORDER BY clause will have to be modified slightly, since it now
applies to the UNION query, not to table FigureTypesNoProject:

SELECT FixtureTypesNoProject.Type
FROM FixtureTypesNoProject
UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1),
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a combobox whose data source is this query, which sorts the
records correctly:

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
;

however, when I add the following (to include the <all> word at the top
of the combolist...) it no longer sorts
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

Anysuggestions about how this couldbe accomplished?

many thanks inadvance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
Hmm. That doesn't make sense to me.

Perhaps you'll have to add some calculated fields to the SQL and sort on
them:

SELECT FixtureTypesNoProject.Type,
IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1) AS Sort1,
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0")) AS Sort2
FROM FixtureTypesNoProject
UNION
Select "< ALL >", -1, -1
FROM [FixtureTypesNoProject]
ORDER BY 2, 3

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
so I changed it back to the way you had it wwritten (I completely
misunderstood what you had meant -my fault, apologies..)
but , i'm still getting the same error...


Douglas J. Steele said:
As I said, because the ORDER BY clause applies to the UNION query, not to
table FixtureTypesNoProject, you cannot put the table name into the ORDER
BY clause.

Are you saying that the SQL statement I suggested gave you the error
message?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
i made the change, but then got a message that my Order By clause
included field(s) that were not selected by the query

i then changed all of the [Type] to be FixtureTypesNoProject.Type , but
still got the same message (see below)

i can't find any that are not (but then i'm not always good at this
stuff...
any additional thoughts?


SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr( FixtureTypesNoProject.Type,"-")=0,Len(
FixtureTypesNoProject.Type),InStr( FixtureTypesNoProject.Type,"-")-1),
IIf(InStr(type,"EM")=0, FixtureTypesNoProject.Type,Replace(
FixtureTypesNoProject.Type,"EM","0"));


----------------------------------------------------------------------------------------------------------------------------------------------------
The ORDER BY clause must always be at the end of Union queries.
However, the ORDER BY clause will have to be modified slightly, since
it now applies to the UNION query, not to table FigureTypesNoProject:

SELECT FixtureTypesNoProject.Type
FROM FixtureTypesNoProject
UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1),
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a combobox whose data source is this query, which sorts the
records correctly:

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
;

however, when I add the following (to include the <all> word at the
top of the combolist...) it no longer sorts
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

Anysuggestions about how this couldbe accomplished?

many thanks inadvance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
well i'm not sure what you did, but it works grrreat. looks like i have
some subway reading to figure it out and learn the lesson
THANKS !


Douglas J. Steele said:
Hmm. That doesn't make sense to me.

Perhaps you'll have to add some calculated fields to the SQL and sort on
them:

SELECT FixtureTypesNoProject.Type,
IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1) AS Sort1,
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0")) AS Sort2
FROM FixtureTypesNoProject
UNION
Select "< ALL >", -1, -1
FROM [FixtureTypesNoProject]
ORDER BY 2, 3

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mark Kubicki said:
so I changed it back to the way you had it wwritten (I completely
misunderstood what you had meant -my fault, apologies..)
but , i'm still getting the same error...


Douglas J. Steele said:
As I said, because the ORDER BY clause applies to the UNION query, not
to table FixtureTypesNoProject, you cannot put the table name into the
ORDER BY clause.

Are you saying that the SQL statement I suggested gave you the error
message?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


i made the change, but then got a message that my Order By clause
included field(s) that were not selected by the query

i then changed all of the [Type] to be FixtureTypesNoProject.Type , but
still got the same message (see below)

i can't find any that are not (but then i'm not always good at this
stuff...
any additional thoughts?


SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr( FixtureTypesNoProject.Type,"-")=0,Len(
FixtureTypesNoProject.Type),InStr( FixtureTypesNoProject.Type,"-")-1),
IIf(InStr(type,"EM")=0, FixtureTypesNoProject.Type,Replace(
FixtureTypesNoProject.Type,"EM","0"));


----------------------------------------------------------------------------------------------------------------------------------------------------
The ORDER BY clause must always be at the end of Union queries.
However, the ORDER BY clause will have to be modified slightly, since
it now applies to the UNION query, not to table FigureTypesNoProject:

SELECT FixtureTypesNoProject.Type
FROM FixtureTypesNoProject
UNION
Select "< ALL >" FROM [FixtureTypesNoProject]
ORDER BY IIf(InStr([Type],"-")=0,Len([type]),InStr([type],"-")-1),
IIf(InStr(type,"EM")=0,[Type],Replace([Type],"EM","0"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a combobox whose data source is this query, which sorts the
records correctly:

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
;

however, when I add the following (to include the <all> word at the
top of the combolist...) it no longer sorts
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY
IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1),
IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0"))
UNION Select "< ALL >" FROM [FixtureTypesNoProject];

Anysuggestions about how this couldbe accomplished?

many thanks inadvance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
Back
Top