How do you sort date and time fields properly and permanently?

  • Thread starter Thread starter Bayou BoB
  • Start date Start date
B

Bayou BoB

I have a form that has two subforms on it. One subform is the medical
schedule, the other subform is the program schedule. Essentially this
form returns all of the events in both of these categories for the
day. Naturally the date field has both the date, and time in it.

The form itself works, and returns the records for that day. However,
they are jumbled timewise. Entries often look like this: (other
information fields ommitted due to lack of relevance in the sort
order, but for info purposes, they include a staff member name, an
activity name, a destination and a company vehicle required check
box).

1/25/2004 3:00:00 PM
1/25/2004 12:00:00 PM
1/25/2004 2:30:00 PM
1/25/2004 11:00:00 AM
1/25/2004 4:00PM

So the records themselves aren't sorting properly. Now I can click on
the date field in the subform and have them re-sort in an Ascending
fashion, and have all be well.... I can go into the scheduling query
and sort the date field in an ascending fashion and have all be
well...until the form or query closes that is, and I have to do this
every time the form or the query loads it seems. It never retains that
sort specification, and hence, if any scheduling event is entered out
of order, which is almost always the case on a daily basis as things
come up, the scheduling form with the two subforms on it comes up in a
jumbled order. How can I ensure that it always comes up in the right
ascending order, and that records that are entered, file themselves
neatly in the right place? This seems so simple and yet it's driving
me nuts! Many thanks for your help.

Kevin

P.S. I do use the function INT([ActDate]) in the query to at least get
part of the order right if that makes any sense. I use that expression
in order to use "Date()" as the criteria so that it pulls up only
today's events in the subforms, and not all of the records for all
days, or no records at all given there is also "time" in the same
field.
 
Bayou BoB said:
I have a form that has two subforms on it. One subform is the medical
schedule, the other subform is the program schedule. Essentially this
form returns all of the events in both of these categories for the
day. Naturally the date field has both the date, and time in it.

The form itself works, and returns the records for that day. However,
they are jumbled timewise. Entries often look like this: (other
information fields ommitted due to lack of relevance in the sort
order, but for info purposes, they include a staff member name, an
activity name, a destination and a company vehicle required check
box).

1/25/2004 3:00:00 PM
1/25/2004 12:00:00 PM
1/25/2004 2:30:00 PM
1/25/2004 11:00:00 AM
1/25/2004 4:00PM

So the records themselves aren't sorting properly. Now I can click on
the date field in the subform and have them re-sort in an Ascending
fashion, and have all be well.... I can go into the scheduling query
and sort the date field in an ascending fashion and have all be
well...until the form or query closes that is, and I have to do this
every time the form or the query loads it seems. It never retains that
sort specification, and hence, if any scheduling event is entered out
of order, which is almost always the case on a daily basis as things
come up, the scheduling form with the two subforms on it comes up in a
jumbled order. How can I ensure that it always comes up in the right
ascending order, and that records that are entered, file themselves
neatly in the right place? This seems so simple and yet it's driving
me nuts! Many thanks for your help.

Kevin

P.S. I do use the function INT([ActDate]) in the query to at least get
part of the order right if that makes any sense. I use that expression
in order to use "Date()" as the criteria so that it pulls up only
today's events in the subforms, and not all of the records for all
days, or no records at all given there is also "time" in the same
field.
Click to expand...

Are your subforms based directly on their respective tables? If so, try
basing them on queries (or use SQL statements in their recordsources)
that sort the records into ascending order by the date/time field. For
example, if the recordsource of one of the subforms were
"tblMedicalSchedule", change it to "SELECT * FROM tblMedicalSchedule
ORDER BY ScheduleDateTime;".

From the sound of it, your actual form/subform/recordsource picture is
somewhat more complicated, but I don't have enough information to be
more specific. If this isn't enough to enable you to solve your
problem, or if I've misunderstood it, please post back.
 
You can either use a Query with ORDER BY clause (on the DateTime Field) as
the RecordSource for the Subform and the selected Records will be ordered on
the Subform accordingly.

Alternatively, you can use the OrderBy and OrderByOn Properties of the
ObjectSource of the SubformControl, i.e. the Form you used as the Subform.
 
Thanks, that has it working.

Kevin


Bayou BoB said:
I have a form that has two subforms on it. One subform is the medical
schedule, the other subform is the program schedule. Essentially this
form returns all of the events in both of these categories for the
day. Naturally the date field has both the date, and time in it.

The form itself works, and returns the records for that day. However,
they are jumbled timewise. Entries often look like this: (other
information fields ommitted due to lack of relevance in the sort
order, but for info purposes, they include a staff member name, an
activity name, a destination and a company vehicle required check
box).

1/25/2004 3:00:00 PM
1/25/2004 12:00:00 PM
1/25/2004 2:30:00 PM
1/25/2004 11:00:00 AM
1/25/2004 4:00PM

So the records themselves aren't sorting properly. Now I can click on
the date field in the subform and have them re-sort in an Ascending
fashion, and have all be well.... I can go into the scheduling query
and sort the date field in an ascending fashion and have all be
well...until the form or query closes that is, and I have to do this
every time the form or the query loads it seems. It never retains that
sort specification, and hence, if any scheduling event is entered out
of order, which is almost always the case on a daily basis as things
come up, the scheduling form with the two subforms on it comes up in a
jumbled order. How can I ensure that it always comes up in the right
ascending order, and that records that are entered, file themselves
neatly in the right place? This seems so simple and yet it's driving
me nuts! Many thanks for your help.

Kevin

P.S. I do use the function INT([ActDate]) in the query to at least get
part of the order right if that makes any sense. I use that expression
in order to use "Date()" as the criteria so that it pulls up only
today's events in the subforms, and not all of the records for all
days, or no records at all given there is also "time" in the same
field.
Click to expand...

Are your subforms based directly on their respective tables? If so, try
basing them on queries (or use SQL statements in their recordsources)
that sort the records into ascending order by the date/time field. For
example, if the recordsource of one of the subforms were
"tblMedicalSchedule", change it to "SELECT * FROM tblMedicalSchedule
ORDER BY ScheduleDateTime;".

From the sound of it, your actual form/subform/recordsource picture is
somewhat more complicated, but I don't have enough information to be
more specific. If this isn't enough to enable you to solve your
problem, or if I've misunderstood it, please post back.
Click to expand...
 
Back
Top