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?

Please advise.

Thanks

Make sure the field is indexed, although with only a few thoousand records 
it might not matter much (today).
Base the listbox on the query.
shmoussa wrote:
> 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?
>
> Please advise.
>
> Thanks 

"shmoussa" <shmoussa@gmail.com> wrote in message 
news:c0a30d69-85ff-46bd-be24-e9ca03967134@k5g2000yqf.googlegroups.com...
> 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.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)