Ken,
I get an invalid syntax at AS.
John
:
OK, let's slightly modify the new sorting field:
CDate(Nz([F2_A_TIME],[F1_A_TIME])) AS Fx_A_TIME_SORT
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Ken,
We are slowly getting there. When it sorts now, it sorts numerically
(ex:
1:51 PM, 10:47 PM, 11:25 AM, 11:46 AM, 2:30 PM, 4:41 PM, etc.). It
is
ignoring the AM and PM. I do have the report sorting and grouping by
date
(Field: F1_A_DATE) first. I probably should have mentioned this
before,
but I
believe this should not cause a problem. I have attached the new SQL
query
statement with the Fx_A_TIME_SORT field.
SELECT DISTINCTROW Participants.STATUS, Participants.TRANS,
Transportation.NAMES, Transportation.TRAVELTOCOOP,
Transportation.F1_D_DATE,
Transportation.F1_D_TIME, Transportation.F1_D_AIRPORT,
Transportation.F1_A_DATE, Transportation.F1_A_TIME,
Transportation.F1_A_AIRPORT, Transportation.F1_FLIGHT,
Transportation.F1_AIRLINE, Transportation.F1_NUMBER,
Transportation.F1_NOTES,
Transportation.F2_D_DATE, Transportation.F2_D_TIME,
Transportation.F2_D_AIRPORT, Transportation.F2_A_DATE,
Transportation.F2_A_TIME, Transportation.F2_A_AIRPORT,
Transportation.F2_FLIGHT, Transportation.F2_AIRLINE,
Transportation.F2_NUMBER, Transportation.F2_NOTES,
Transportation.F3_D_DATE,
Transportation.F3_D_TIME, Transportation.F3_D_AIRPORT,
Transportation.F3_A_DATE, Transportation.F3_A_TIME,
Transportation.F3_A_AIRPORT, Transportation.F3_FLIGHT,
Transportation.F3_AIRLINE, Transportation.F3_NUMBER,
Transportation.F3_NOTES,
Nz([F2_A_TIME],[F1_A_TIME]) AS Fx_A_TIME_SORT
FROM Participants INNER JOIN Transportation ON Participants.PID =
Transportation.PID
WHERE (((Participants.STATUS)="A") AND ((Transportation.NAMES)>" ")
AND
((Transportation.TRAVELTOCOOP)=Yes));
Hopefully this helps. Thanks again for all your help. I will be off
Friday,
so I won't be able to get back to you until Monday.
John
:
Open the query in design view. Go to the first empty column at
right
side
of
grid. Put this expression in the Field: box:
Fx_A_TIME_SORT: Nz(F2_A_TIME, F1_A_TIME)
Save the query. Now the Fx_A_TIME_SORT field will be available to
your
report to use for sorting.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Ken,
Thanks for the info. I understand what you are talking about, but
I
have
never added a calculated field in a query. I did try and add one,
but I
am
unsure exactly what I need to put in the field for the
calculation.
What
would be the expression be that I add to the query? Thanks.
John
:
Aha, ok I now understand better what you're wanting to do. The
sorting
on
the report is working correctly because the person with the
two-leg
flight
has an earlier time for the F1_A_TIME field than the other
person.
So
the
two-leg person sorts first.
So I suggest that you add a calculated field (Fx_A_TIME_SORT) to
your
query,
which will replace F1_A_TIME with F2_A_TIME when F2_A_TIME is
not
NULL:
SELECT DISTINCTROW Participants.STATUS, Participants.TRANS,
Transportation.NAMES, Transportation.TRAVELTOCOOP,
Transportation.F1_D_DATE,
Transportation.F1_D_TIME, Transportation.F1_D_AIRPORT,
Transportation.F1_A_DATE, Transportation.F1_A_TIME,
Transportation.F1_A_AIRPORT, Transportation.F1_FLIGHT,
Transportation.F1_AIRLINE, Transportation.F1_NUMBER,
Transportation.F1_NOTES,
Transportation.F2_D_DATE, Transportation.F2_D_TIME,
Transportation.F2_D_AIRPORT, Transportation.F2_A_DATE,
Transportation.F2_A_TIME, Transportation.F2_A_AIRPORT,
Transportation.F2_FLIGHT, Transportation.F2_AIRLINE,
Transportation.F2_NUMBER, Transportation.F2_NOTES,
Transportation.F3_D_DATE,
Transportation.F3_D_TIME, Transportation.F3_D_AIRPORT,
Transportation.F3_A_DATE, Transportation.F3_A_TIME,
Transportation.F3_A_AIRPORT, Transportation.F3_FLIGHT,
Transportation.F3_AIRLINE, Transportation.F3_NUMBER,
Transportation.F3_NOTES,
Nz(F2_A_TIME, F1_A_TIME) AS Fx_A_TIME_SORT
FROM Participants INNER JOIN Transportation ON Participants.PID
=
Transportation.PID
WHERE (((Participants.STATUS)="A") AND ((Transportation.NAMES)>"
")
AND
((Transportation.TRAVELTOCOOP)=Yes));
Then use the Fx_A_TIME_SORT field as the sorting field in your
report,
instead of F1_A_TIME_SORT and F2_A_TIME_SORT.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Ken,
All the F#_A_TIME are Date/Time Data Type fields. The
information,
for
the
report, is being pulled from a Query. I have pasted the SQL
statement
for
the
query below:
SELECT DISTINCTROW Participants.STATUS, Participants.TRANS,
Transportation.NAMES, Transportation.TRAVELTOCOOP,
Transportation.F1_D_DATE,
Transportation.F1_D_TIME, Transportation.F1_D_AIRPORT,
Transportation.F1_A_DATE, Transportation.F1_A_TIME,
Transportation.F1_A_AIRPORT, Transportation.F1_FLIGHT,
Transportation.F1_AIRLINE, Transportation.F1_NUMBER,
Transportation.F1_NOTES,
Transportation.F2_D_DATE, Transportation.F2_D_TIME,
Transportation.F2_D_AIRPORT, Transportation.F2_A_DATE,
Transportation.F2_A_TIME, Transportation.F2_A_AIRPORT,
Transportation.F2_FLIGHT, Transportation.F2_AIRLINE,
Transportation.F2_NUMBER, Transportation.F2_NOTES,
Transportation.F3_D_DATE,
Transportation.F3_D_TIME, Transportation.F3_D_AIRPORT,
Transportation.F3_A_DATE, Transportation.F3_A_TIME,
Transportation.F3_A_AIRPORT, Transportation.F3_FLIGHT,
Transportation.F3_AIRLINE, Transportation.F3_NUMBER,
Transportation.F3_NOTES
FROM Participants INNER JOIN Transportation ON
Participants.PID =
Transportation.PID
WHERE (((Participants.STATUS)="A") AND
((Transportation.NAMES)>"
")
AND
((Transportation.TRAVELTOCOOP)=Yes));
Thanks.
John
:
The sorting that you're seeing indicates that the F1_A_TIME
field
is
being
seen as a text field, not a datetime field. What is the
Datatype
of
the
field in the table?
Are you using the table as the RecordSource for the report?
If
not,
post
the
SQL statement of the query that you're using for the report.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
I have a report that I use for airline flights. I am looking
for a
way
to
sort multiple time fields for connecting flights, by final
arrival
time.
As
an example: Person A has a direct flight leaving at 9:05AM
(Field:
F1_D_TIME)
and arriving at 11:45AM (Field: F1_A_TIME), Person B has a
flight
leaving
at
8:25AM (Field: F1_D_TIME) and arriving at the connecting
airport
at
10:25AM
(Field: F1_A_TIME), then leaving the connecting airport for
the
last
leg
at
11:45AM (Field: F2_D_TIME) and arriving at 1:55PM (Field:
F2_A_TIME).
When
using Sorting and Grouping in the report, I have F1_A_TIME
sorting
first
and
F2_A_TIME sorting second. What happens is Person B (1:55PM)
comes
first
and
Person A (11:45AM) comes second. What I need is to have
Person
A
(11:45AM)
first, then Person B (1:55PM) second. This is only a small
example,
as
I
have
multiple people with flights in the database. Any help on
this
would
be
greatly appreciated. Thanks.
John
