Viewing date fields in time order

[Guest]

If a report is created based on a table with date fields, then, for any given
date, those fields can be printed in time order. If the table is viewed
directly, however, it does not appear possible to sort the date fields in
time order. When filtering and viewing a particular set of records from a
table, for example, the record order for a given date remains unchanged
whether they are sorted in ascending or descending order. In addition, if
there are, say, 5 records for January 15th, they are listed in the order 1 to
5, even though record 1 may have been created after record 3. This can make
the table difficult to interpret but it is not always convenient to create a
report when all that is needed is a cursory ad hoc look at a small set of
records. How can this problem be resolved?

 

[Duane Hookom]

It isn't clear what your issue is. You can sort date/time fields to place
them in chronological order. Time can be stored in the date field. Is this
how your values are stored? You can never assume that records will appear in
the order they are entered.

Are you storing just the date or the date and time?

 

[Guest]

Thanks for the response.

The date fields in the tables concerned are defined as type Date/Time. I
therefore assume that both date and time elements are stored before and after
the dp respectively. If the table is viewed, and the fields sorted, I thus
reasoned that they would be listed in date/time order - ie for any given
date, the earliest records would be listed first in ascending order and vice
versa for descending, but this is not the case. The dates are certainly
sorted in the expected order but, for a given date, the time order appears to
be random and remains so whether the sort is in ascending or descending order
- ie no time sort appears to take place. If the table is used as the basis
for a report, however, the expected time sort order does result. I have
changed the formatting from short date to general date, since the latter
specifically indicates that this includes the time element, but it did not
appear to have any effect. To be honest, I did not expect it to because,
unless I am mistaken, formatting only effects the way in which a field is
displayed, not its underlying properties. Am I right in this assumption? I
remain rather baffled with this one and the question therefore remains as to
how I should define these date fields in the table so that they are listed in
time order when the table is viewed. Does it perhaps depend on the way the
date field is entered on the form from which the table is populated? Should
I used Now() instead of Date(), for example?

 

[Duane Hookom]

The order you view records in a table or query has no significant affect on
the way they appear in reports. You need to use the Sorting and Grouping
dialog from the report design view.

I create 90% of my formatting in form and report controls, not tables and
queries.

If you want a field to default to a date and time, use Now() since Date()
will store only the date portion.