Sorting By Date

[cna48]

I have a table that I would like to query and then sort by date field. All of
the 10/2/08 dates get sorted together as they should. What if I wanted to
add a specific hour to all of these dates in order to sort them to a more
accurate order? How would I format the original [Field] in the table? I
tried general date and then mm/dd/yyyy-hh, but then I can't figure out how it
wants me to input the date and hour?

Thanks in advance,

Candy

 

[John W. Vinson]

I have a table that I would like to query and then sort by date field. All of
the 10/2/08 dates get sorted together as they should. What if I wanted to
add a specific hour to all of these dates in order to sort them to a more
accurate order? How would I format the original [Field] in the table? I
tried general date and then mm/dd/yyyy-hh, but then I can't figure out how it
wants me to input the date and hour?

Thanks in advance,

Candy

Formatting has nothing to do with sorting. A Date/Time value - regardless of
format - is stored as a Double Float number, a count of days and fractions of
a day (times) since midnight, December 30, 1899. If the fractional part is
zero (as seems likely in this case) the time is midnight at the start of the
day. The Format of a date field merely controls how the stored value is
displayed.

You can enter dates with times in a variety of formats, such as

10/9/08 11:16am

but I'm not sure you can enter *just* an hour without entering minutes.

 

[Armen Stein]

I have a table that I would like to query and then sort by date field. All of
the 10/2/08 dates get sorted together as they should. What if I wanted to
add a specific hour to all of these dates in order to sort them to a more
accurate order? How would I format the original [Field] in the table? I
tried general date and then mm/dd/yyyy-hh, but then I can't figure out how it
wants me to input the date and hour?

Hi Candy,

Access date fields can always store both date and time values. It's
up to you to enter them. You can type 10/9/2008 10:00am into a field
and both the date and time components will be stored.

The format of a date field governs how it is displayed, not how it is
entered or stored. You can use format: mm/dd/yyyy hham/pm if you
want to see it displayed as 10/09/2008 10am.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[cna48]

Thank you John,

This is exactly the answer I needed. I understand the function
of time, I just could not figur out how the heck it wanted the data inputed.
This allows me to sort them according to the exact minute within that day and
in what priority. Thanks to you and all that post.

I have a table that I would like to query and then sort by date field. All of
the 10/2/08 dates get sorted together as they should. What if I wanted to
add a specific hour to all of these dates in order to sort them to a more
accurate order? How would I format the original [Field] in the table? I
tried general date and then mm/dd/yyyy-hh, but then I can't figure out how it
wants me to input the date and hour?

Thanks in advance,

Candy

Formatting has nothing to do with sorting. A Date/Time value - regardless of
format - is stored as a Double Float number, a count of days and fractions of
a day (times) since midnight, December 30, 1899. If the fractional part is
zero (as seems likely in this case) the time is midnight at the start of the
day. The Format of a date field merely controls how the stored value is
displayed.

You can enter dates with times in a variety of formats, such as

10/9/08 11:16am

but I'm not sure you can enter *just* an hour without entering minutes.

 

[John W. Vinson]

Thank you John,

This is exactly the answer I needed. I understand the function
of time, I just could not figur out how the heck it wanted the data inputed.
This allows me to sort them according to the exact minute within that day and
in what priority. Thanks to you and all that post.

If it's appropriate, you can even set the DefaultValue of the table field, or
of the textbox on the form used for input, to =Now(); this will automatically
fill in the current date and time as of the instant the record was created. If
needed (e.g. if you're entering data written down previously) you can overtype
that value.