sorts query while including <all> at top of list

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
 
D

Douglas J. Steele

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"))
 
M

Mark Kubicki

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

Douglas J. Steele

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

Mark Kubicki

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

Douglas J. Steele

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

Mark Kubicki

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

union query with "unique" and "is not null" values 1
subquery without using EXISTS word (?) 1
How to extract portion of text 5
Memo Field Truncation 1
Access 2003 Copy a record when a field contains three entries 4
SQL syntax 16
Multi-Selection Code review 2
Remove Quotes within Data Values 1

Top