Re: Sort Multi Time Fields


Ken,

I get an invalid syntax at AS.

John


"Ken Snell [MVP]" wrote:

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/




"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E02BEE0A-7887-4DD5-A3F5-156C5B22D511@xxxxxxxxxxxxxxxx
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


"Ken Snell [MVP]" wrote:

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/




"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7E214DE8-4A39-4BA2-BFC9-9DE487CEDACF@xxxxxxxxxxxxxxxx
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


"Ken Snell [MVP]" wrote:

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/



"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:173395A9-9159-4275-9991-AE45035948F5@xxxxxxxxxxxxxxxx
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


"Ken Snell [MVP]" wrote:

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/


"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:29349816-2D3A-46F9-9608-BBE996F88B73@xxxxxxxxxxxxxxxx
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












.

Relevant Pages

  • Re: Best way to import text file into existing table on a daily ba
    ... One is a title of the report and the second row are field names. ... "Ken Snell " wrote: ... permanent table -- although having them the same will allow the query design ... <MS ACCESS MVP> ...
    (microsoft.public.access.externaldata)
  • Re: Sort Multi Time Fields
    ... Ken Snell ... <MS ACCESS MVP> ... I do have the report sorting and grouping by ... Save the query. ...
    (microsoft.public.access.reports)
  • Re: Crosstab Report
    ... MS Access MVP ... some of the information you posted to the query and found it very useful. ... would like to choose the year the report starts. ... Specify the column names in the query's Column Headings ...
    (microsoft.public.access.reports)
  • Re: Crosstab Report
    ... MS Access MVP ... I try to write the report my headings/totals crash. ... "Duane Hookom" wrote: ... some of the information you posted to the query and found it very ...
    (microsoft.public.access.reports)
  • Re: Sort Multi Time Fields
    ... When it sorts now, it sorts numerically (ex: ... I do have the report sorting and grouping by date ... Save the query. ...
    (microsoft.public.access.reports)
Loading