How to index by the date?

[Martin]

I have a field which is date type, looks like [ yyyy-mm-dd ] . I want to
index it in the table, the index is based on the "dd" not based on
"yyyy-mm-dd". How to do it?

For example


2005-07-25
2004-08-03
2006-12-16

after index ( the result):
(because I want to index only by "dd", as: 03 < 16 < 25 )

2004-08-03
2006-12-16
2005-07-25

 

[Allen Browne]

Internally, Access treats date/time values as real numbers, where the
integer part represents the date, and the fraction the time (e.g. 0.5 =
noon; 0.25 = 6am (one quarter of a day).) Therefore, when you index the
field, Access is indexing the full date/time value, not just the day part of
the field.

To index by day, you would need to use 3 separate fields for the day, month
and year.

Just to be clear, you can still *sort* by the day if you create a query and
type this expression into the Field row:
Day([Field1])
Replace Field1 with the name of your date/time field, and then choose
Ascending in the sorting row in your query.

 

[Guest]

Use a select query and sort on --
Day of Month: DatePart("d",[YourDateField])

 

[uriel]

I have a field which is date type, looks like [ yyyy-mm-dd ] . I want to
index it in the table, the index is based on the "dd" not based on
"yyyy-mm-dd". How to do it?

For example


2005-07-25
2004-08-03
2006-12-16

after index ( the result):
(because I want to index only by "dd", as: 03 < 16 < 25 )

2004-08-03
2006-12-16
2005-07-25

 

[Martin]

Thank you very much!