Order By Properties

[bw]

In the Order By Properties of my form, I have the following (FromAirport
and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems to
work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

Can someone show me the correct syntax?

Thanks,
Bernie


--

 

[John Vinson]

In the Order By Properties of my form, I have the following (FromAirport
and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems to
work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

Can someone show me the correct syntax?

Thanks,
Bernie

You cannot sort a Form except by fields in that Form's Recordsource. A
combo box is not part of the recordsource.

Can you base the Form on a Query joining the Airport table (twice)?
That would get you the airport's name rather than just its ID.

John W. Vinson[MVP]

 

[bw]

In the Order By Properties of my form, I have the following
(FromAirport
and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems to
work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

Can someone show me the correct syntax?

Thanks,
Bernie

You cannot sort a Form except by fields in that Form's Recordsource. A
combo box is not part of the recordsource.

Can you base the Form on a Query joining the Airport table (twice)?
That would get you the airport's name rather than just its ID.

John W. Vinson[MVP]

Thank you sir!

I'll try to do that.
Bernie

 

[Allen Browne]

Create a query that uses both the table you currently have the form bound
to, and also the table that supplies the values for the combo. Set the
RecordSource property of your form to this query.

Now you have the fields you want to sort on in your query, and you can use
the Sorting row to sort them as you wish.

 

[bw]

Thanks Allen!
I'm getting so confused. I'm not making any progress at all. If you
(or anyone) wants to help, please look at the SQL statement for my Form,
and then maybe you will see why I'm having a problem. Keep in mind that
everything works "perfectly", except that I want to sort by the Airport
Name (From and To), and not their ID's. Thanks for any help you can
provide...

SELECT tblFlightSchedule.FltNum, tblAirports.Airport,
tblAirports_1.Airport, tblFlightSchedule.FromAirport,
tblFlightSchedule.ToAirport, tblAirlines.txtAirline,
tblFlightSchedule.Via, tblFlightSchedule.DepartTime,
tblFlightSchedule.ArrivalTime, tblAirports.TimeZone,
tblAirports_1.TimeZone, [tblAirports.TimeZone] AS FromA,
[tblAirports_1.TimeZone] AS ToA,
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))
AS DTimeFactor,
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))
AS RTimeFactor,
CVDate(Nz(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),Time()))
AS TotalTime
FROM tblAirports INNER JOIN (tblAirlines INNER JOIN (tblFlightSchedule
INNER JOIN tblAirports AS tblAirports_1 ON tblFlightSchedule.ToAirport =
tblAirports_1.AirportID) ON tblAirlines.AirlineID =
tblFlightSchedule.AirlineID) ON tblAirports.AirportID =
tblFlightSchedule.FromAirport
WHERE (((tblFlightSchedule.FltNum)<>"N/A"));

Create a query that uses both the table you currently have the form
bound to, and also the table that supplies the values for the combo.
Set the RecordSource property of your form to this query.

Now you have the fields you want to sort on in your query, and you can
use the Sorting row to sort them as you wish.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In the Order By Properties of my form, I have the following
(FromAirport and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems to
work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

 

[Allen Browne]

Do you have the From and To names in a table?
Is that table in the query?
Can you choose Ascending in the Sorting row of the query under those fields?

Or add an ORDER BY clause to the SQL if you prefer to work that way?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen!
I'm getting so confused. I'm not making any progress at all. If you (or
anyone) wants to help, please look at the SQL statement for my Form, and
then maybe you will see why I'm having a problem. Keep in mind that
everything works "perfectly", except that I want to sort by the Airport
Name (From and To), and not their ID's. Thanks for any help you can
provide...

SELECT tblFlightSchedule.FltNum, tblAirports.Airport,
tblAirports_1.Airport, tblFlightSchedule.FromAirport,
tblFlightSchedule.ToAirport, tblAirlines.txtAirline,
tblFlightSchedule.Via, tblFlightSchedule.DepartTime,
tblFlightSchedule.ArrivalTime, tblAirports.TimeZone,
tblAirports_1.TimeZone, [tblAirports.TimeZone] AS FromA,
[tblAirports_1.TimeZone] AS ToA,
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))
AS DTimeFactor,
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))
AS RTimeFactor,
CVDate(Nz(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),Time()))
AS TotalTime
FROM tblAirports INNER JOIN (tblAirlines INNER JOIN (tblFlightSchedule
INNER JOIN tblAirports AS tblAirports_1 ON tblFlightSchedule.ToAirport =
tblAirports_1.AirportID) ON tblAirlines.AirlineID =
tblFlightSchedule.AirlineID) ON tblAirports.AirportID =
tblFlightSchedule.FromAirport
WHERE (((tblFlightSchedule.FltNum)<>"N/A"));

Create a query that uses both the table you currently have the form bound
to, and also the table that supplies the values for the combo. Set the
RecordSource property of your form to this query.

Now you have the fields you want to sort on in your query, and you can
use the Sorting row to sort them as you wish.

In the Order By Properties of my form, I have the following (FromAirport
and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems to
work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

 

[bw]

Field Explanations:
tblAirports.Airport is the From Airport. This is the Airport Name, not
ID.
tblAirports_1.Airport is the To Airport. This is the Airport Name, not
ID.
FromAirport and ToAirport are the Airport ID's.
I Can sort Any of these in the Sorting row of the query under those
fields.

However, I ALSO want to sort "TotalTime".
So when I also sort in the Sorting row of the query under this field, I
get a message that wants me to enter a parameter for DTimeFactor and
RTimeFactor???? If I don't sort this field, the query works okay???

Since this doesn't work (and I don't know why), I have removed all
sorting from the Sorting row of the query.

So as an poor work around, I have been sorting by placing the following
code in the "Order By" property there:
[FromAirport], [ToAirport], [TotalTime] DESC, which works fine (not the
right field, but it works).

Tell me how to sort by "TotalTime" in the Sorting row of the query (and
why it works ok in the "Order By" property).
OR, tell me How to sort the way I want using the "Order By" property.

Thanks for you help...
Bernie

Do you have the From and To names in a table?
Is that table in the query?
Can you choose Ascending in the Sorting row of the query under those
fields?

Or add an ORDER BY clause to the SQL if you prefer to work that way?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen!
I'm getting so confused. I'm not making any progress at all. If you
(or anyone) wants to help, please look at the SQL statement for my
Form, and then maybe you will see why I'm having a problem. Keep in
mind that everything works "perfectly", except that I want to sort by
the Airport Name (From and To), and not their ID's. Thanks for any
help you can provide...

SELECT tblFlightSchedule.FltNum, tblAirports.Airport,
tblAirports_1.Airport, tblFlightSchedule.FromAirport,
tblFlightSchedule.ToAirport, tblAirlines.txtAirline,
tblFlightSchedule.Via, tblFlightSchedule.DepartTime,
tblFlightSchedule.ArrivalTime, tblAirports.TimeZone,
tblAirports_1.TimeZone, [tblAirports.TimeZone] AS FromA,
[tblAirports_1.TimeZone] AS ToA,
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))
AS DTimeFactor,
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))
AS RTimeFactor,
CVDate(Nz(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),Time()))
AS TotalTime
FROM tblAirports INNER JOIN (tblAirlines INNER JOIN
(tblFlightSchedule INNER JOIN tblAirports AS tblAirports_1 ON
tblFlightSchedule.ToAirport = tblAirports_1.AirportID) ON
tblAirlines.AirlineID = tblFlightSchedule.AirlineID) ON
tblAirports.AirportID = tblFlightSchedule.FromAirport
WHERE (((tblFlightSchedule.FltNum)<>"N/A"));

Create a query that uses both the table you currently have the form
bound to, and also the table that supplies the values for the combo.
Set the RecordSource property of your form to this query.

Now you have the fields you want to sort on in your query, and you
can use the Sorting row to sort them as you wish.

In the Order By Properties of my form, I have the following
(FromAirport and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems
to work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

 

[Allen Browne]

Okay, so you are trying to use the result of a calculated field in another
calculated field.

That works in some circumstances, but generally not if you are trying to
sort or use criteria on the resulting calculation. The workaround is to
repeat the entire calculation, instead of using the calculated field name.

Would it be possible to add a field to your Airport table to store the
timezone? For example, this field would have 1 for Hawaii, 2 for Alaska,
etc. You could then use this field in the TotalTime calculation instead of
needed the nested IIf() expressions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Field Explanations:
tblAirports.Airport is the From Airport. This is the Airport Name, not
ID.
tblAirports_1.Airport is the To Airport. This is the Airport Name, not
ID.
FromAirport and ToAirport are the Airport ID's.
I Can sort Any of these in the Sorting row of the query under those
fields.

However, I ALSO want to sort "TotalTime".
So when I also sort in the Sorting row of the query under this field, I
get a message that wants me to enter a parameter for DTimeFactor and
RTimeFactor???? If I don't sort this field, the query works okay???

Since this doesn't work (and I don't know why), I have removed all sorting
from the Sorting row of the query.

So as an poor work around, I have been sorting by placing the following
code in the "Order By" property there:
[FromAirport], [ToAirport], [TotalTime] DESC, which works fine (not the
right field, but it works).

Tell me how to sort by "TotalTime" in the Sorting row of the query (and
why it works ok in the "Order By" property).
OR, tell me How to sort the way I want using the "Order By" property.

Thanks for you help...
Bernie

Do you have the From and To names in a table?
Is that table in the query?
Can you choose Ascending in the Sorting row of the query under those
fields?

Or add an ORDER BY clause to the SQL if you prefer to work that way?
--

Thanks Allen!
I'm getting so confused. I'm not making any progress at all. If you
(or anyone) wants to help, please look at the SQL statement for my Form,
and then maybe you will see why I'm having a problem. Keep in mind that
everything works "perfectly", except that I want to sort by the Airport
Name (From and To), and not their ID's. Thanks for any help you can
provide...

SELECT tblFlightSchedule.FltNum, tblAirports.Airport,
tblAirports_1.Airport, tblFlightSchedule.FromAirport,
tblFlightSchedule.ToAirport, tblAirlines.txtAirline,
tblFlightSchedule.Via, tblFlightSchedule.DepartTime,
tblFlightSchedule.ArrivalTime, tblAirports.TimeZone,
tblAirports_1.TimeZone, [tblAirports.TimeZone] AS FromA,
[tblAirports_1.TimeZone] AS ToA,
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))
AS DTimeFactor,
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))
AS RTimeFactor,
CVDate(Nz(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),Time()))
AS TotalTime
FROM tblAirports INNER JOIN (tblAirlines INNER JOIN (tblFlightSchedule
INNER JOIN tblAirports AS tblAirports_1 ON tblFlightSchedule.ToAirport =
tblAirports_1.AirportID) ON tblAirlines.AirlineID =
tblFlightSchedule.AirlineID) ON tblAirports.AirportID =
tblFlightSchedule.FromAirport
WHERE (((tblFlightSchedule.FltNum)<>"N/A"));

Create a query that uses both the table you currently have the form
bound to, and also the table that supplies the values for the combo.
Set the RecordSource property of your form to this query.

Now you have the fields you want to sort on in your query, and you can
use the Sorting row to sort them as you wish.

In the Order By Properties of my form, I have the following
(FromAirport and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems to
work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...

 

[bw]

WONDERFUL! ! !

I guess it was one of those things staring me in the face. I just could
not see it.
It was an easy change, as I did just as you suggested, and made each
TimeZone correspond to the proper number in the table.

I, and I assume others probably don't give you guys enough information
to help you help us. It's because we don't want to overwhelm you with
what you may consider useless information.

Anyway, thanks for staying with me. Thanks for looking at the data I
sent you. Thanks for providing the solution!!!
Bernie

Okay, so you are trying to use the result of a calculated field in
another calculated field.

That works in some circumstances, but generally not if you are trying
to sort or use criteria on the resulting calculation. The workaround
is to repeat the entire calculation, instead of using the calculated
field name.

Would it be possible to add a field to your Airport table to store the
timezone? For example, this field would have 1 for Hawaii, 2 for
Alaska, etc. You could then use this field in the TotalTime
calculation instead of needed the nested IIf() expressions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Field Explanations:
tblAirports.Airport is the From Airport. This is the Airport Name,
not ID.
tblAirports_1.Airport is the To Airport. This is the Airport Name,
not ID.
FromAirport and ToAirport are the Airport ID's.
I Can sort Any of these in the Sorting row of the query under those
fields.

However, I ALSO want to sort "TotalTime".
So when I also sort in the Sorting row of the query under this field,
I get a message that wants me to enter a parameter for DTimeFactor
and RTimeFactor???? If I don't sort this field, the query works
okay???

Since this doesn't work (and I don't know why), I have removed all
sorting from the Sorting row of the query.

So as an poor work around, I have been sorting by placing the
following code in the "Order By" property there:
[FromAirport], [ToAirport], [TotalTime] DESC, which works fine (not
the right field, but it works).

Tell me how to sort by "TotalTime" in the Sorting row of the query
(and why it works ok in the "Order By" property).
OR, tell me How to sort the way I want using the "Order By" property.

Thanks for you help...
Bernie

Do you have the From and To names in a table?
Is that table in the query?
Can you choose Ascending in the Sorting row of the query under those
fields?

Or add an ORDER BY clause to the SQL if you prefer to work that way?
--

Thanks Allen!
I'm getting so confused. I'm not making any progress at all. If
you (or anyone) wants to help, please look at the SQL statement for
my Form, and then maybe you will see why I'm having a problem.
Keep in mind that everything works "perfectly", except that I want
to sort by the Airport Name (From and To), and not their ID's.
Thanks for any help you can provide...

SELECT tblFlightSchedule.FltNum, tblAirports.Airport,
tblAirports_1.Airport, tblFlightSchedule.FromAirport,
tblFlightSchedule.ToAirport, tblAirlines.txtAirline,
tblFlightSchedule.Via, tblFlightSchedule.DepartTime,
tblFlightSchedule.ArrivalTime, tblAirports.TimeZone,
tblAirports_1.TimeZone, [tblAirports.TimeZone] AS FromA,
[tblAirports_1.TimeZone] AS ToA,
IIf([FromA]="Hawaii",1,IIf([FromA]="Alaska",2,IIf([FromA]="Pacific",3,IIf([FromA]="Mountain",4,IIf([FromA]="Central",5,IIf([FromA]="Eastern",6))))))
AS DTimeFactor,
IIf([ToA]="Hawaii",1,IIf([ToA]="Alaska",2,IIf([ToA]="Pacific",3,IIf([ToA]="Mountain",4,IIf([ToA]="Central",5,IIf([ToA]="Eastern",6))))))
AS RTimeFactor,
CVDate(Nz(DateAdd("h",[RTimeFactor],[DepartTime])-DateAdd("h",[DTimeFactor],[ArrivalTime]),Time()))
AS TotalTime
FROM tblAirports INNER JOIN (tblAirlines INNER JOIN
(tblFlightSchedule INNER JOIN tblAirports AS tblAirports_1 ON
tblFlightSchedule.ToAirport = tblAirports_1.AirportID) ON
tblAirlines.AirlineID = tblFlightSchedule.AirlineID) ON
tblAirports.AirportID = tblFlightSchedule.FromAirport
WHERE (((tblFlightSchedule.FltNum)<>"N/A"));

Create a query that uses both the table you currently have the
form bound to, and also the table that supplies the values for the
combo. Set the RecordSource property of your form to this query.

Now you have the fields you want to sort on in your query, and you
can use the Sorting row to sort them as you wish.

In the Order By Properties of my form, I have the following
(FromAirport and ToAirport are both combo boxes):

[FromAirport], [ToAirport], [TotalTime] DESC

This works fine, except that I want to Sort by Column(2) of both
[FromAirport] and [ToAirport]

I have tried about every combination of things, but nothing seems
to work. For example, [FromAirport].Column(2) doesn't work,
[FromAirport].[Column(2)] doesn't work, etc, etc...