shmoussa said:
Hi,
Is there anyway to get a list of the past 7 days in a listbox?
Currently, I have a table with the next 2,000 days- and a query
pulling out the past 7 days. I think this is slowing down my database
and was wondering if there was a way to do this via VBA?
There are several ways you might do this. One simple way is to set the list
box's RowSource to a value list containing the dates. You would do this in
the form's Open (or Load) event, like this:
'------ start of code ------
Private Sub Form_Open(Cancel As Integer)
Dim I As Integer
Dim dtDate As Date
Dim strDates As String
dtDate = Date
For I = 1 To 7
strDates = strDates & ";" & Format(dtDate, "short date")
dtDate = DateAdd("d", -1, dtDate)
Next I
With Me.lstDates
.RowSourceType = "Value List"
.RowSource = Mid$(strDates, 2)
End With
End Sub
'------ end of code ------
In the above, "lstDates" is the name of the list box.
Another way would be to use a custom rowsource function. That seems to me
to be more complex than necessary for this purpose.
Yet a third, code-free, way would be to have a table whose sole purpose is
to generate records in queries. Such tables are handy for a number of
purposes. This table would have a single field of type Number/Long Integer,
with the records numbered starting at 0 and incrementing by 1 for each
record. It would contain only 10 records (numbered 0 to 9) -- or maybe 100
records if you most often need more than 10 records, but for our current
purposes we only need 10.
With such a table you can use a query to create as many records as you need.
If you need more records than there are in the table, you just add the table
to the query twice, without joining the two copies, and you get 10 * 10 =
100 records (or 100 * 100 = 10,000 records). But right now we only need 10
records.
For example, suppose you name the table Iotas, and the field in the table is
named Iota. Then you could have your list box's RowSourceType property set
to "Table/Query", and use this query for its RowSource:
SELECT Date()-[Iota] AS DateWanted
FROM Iotas
WHERE Iota)<7
ORDER BY Iota;
That will gve you the last 7 days, starting today and ordered downward from
today to 6 days ago.
