Drop Down Date List

[Ampridge]

I can't seem to figure out how to make a drop down date list in a combo box
that lists the last seven days. Obviously, I can type in the last seven days
but then next week the dates are wrong. I don't want to base this on another
field, I just want to look up today's date like a default value range like
=Date(), Date()-1, Date()-2, etc. I know this method does not work but it
seems the best way to make the example. Any ideas would be appreciated.

 

[John Spencer]

One method would be to use a UNION query as the row source of the combobox.
The query would look like the following. For efficiency sake, try to use a
table with one or just a few records that will not grow significantly over time.

SELECT Date()
FROM [SomeSmallTable]
UNION
SELECT Date()-1
FROM [SomeSmallTable]
UNION
SELECT Date()-2
FROM [SomeSmallTable]
UNION
SELECT Date()-3
FROM [SomeSmallTable]
UNION
SELECT Date()-4
FROM [SomeSmallTable]
UNION
SELECT Date()-5
FROM [SomeSmallTable]
UNION
SELECT Date()-6
FROM [SomeSmallTable]

I often have a table of dates (one unique date per record) and information on
the dates (workday, holiday, etc) in my databases. It comes in handy for this
type of thing and for returning counts of workdays between two dates. Or
returning the last Friday of the month, etc.

If you had that table you could use a very simple query to return the week or
the last 10 days or the last ten work days or the workdays for the last two weeks.

SELECT TheDate
FROM CalendarTable
WHERE TheDate Between Date()-6 And Date()
ORDER BY TheDate



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County