Create a list of dates from a query

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

Can anyone tell me a way to create a list of dates by running a query in
access 2003. I don't want to create a table. I just want to run a query and
list 15 days sequentially. For instance I want my result set to be 4 days
prior to today, today and the next 10 days.
 
The information will need to come from somewhere.

Use a counting table if you wish. This link contains some simple
instructions for setting it up, and some code to populate it:
http://allenbrowne.com/ser-39.html
(The article has another purpose, but these tables are useful for lots of
things.

You can then generate a query as:
SELECT DateAdd("d", tblCount.CountID - 4, Date()) AS TheDate
FROM tblCount
WHERE tblCount.CountID Between 1 And 15;
 
Thanks,
Do you know of any functions that would create the daterange. I know the
now() function gives me today, but I would think there is some function or
system table that might give me a range I can select from. I always try to
use whats available from the system before creating counting tables.
 
Thanks,
Where can I get the daterange from. Is there a function that will provide
it or a system table? I don't want to create a counting table.

raskew via AccessMonster.com said:
The WHERE portion of your query would be:
WHERE [DateField] Between (Date()-4) AND (Date()+10)

...so, build your query, and in the criteria cell of your DateField,
type: Between (Date()-4) AND (Date()+10)

HTH - Bob
Can anyone tell me a way to create a list of dates by running a query in
access 2003. I don't want to create a table. I just want to run a query and
list 15 days sequentially. For instance I want my result set to be 4 days
prior to today, today and the next 10 days.
Click to expand...
Click to expand...
 
How do you plan to get multiple records out of a function such as Date()?

That's not going to work.
 
I don't understand your question.

When your SELECT query statement calls the function, the code returns a
value. That's one row. Where will the other 14 rows come from if you want 15
records?

I don't think I can help you further.
 
Thanks for your time. I used to work with sql that I could loop in. I don't
remember that type of sql, but I guess access isn't capable. I appreciate
your time.
 
Were do you want to use the list of dates? If it’s in a list box or combo
box, then set the RowSourceType property of the control to 'Value List' and
out the following in the form's Open event procedure:

Dim dtmDate As Date

For dtmDate = VBA.Date - 4 To VBA.Date + 10
Me.lstDates.AddItem dtmDate
Next dtmDate

where lstDates is the name of the control.


If you really want to open a temporary query call the following procedure:

Public Sub OpenDateListQuery()

Const conQDF = "qryDateList"
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim dtmDate As Date

Set dbs = CurrentDb

' delete DateList table if exists
On Error Resume Next
dbs.TableDefs.Delete "DateList"
On Error GoTo 0

' create temporary table
strSQL = "CREATE TABLE DateList (" & _
"DateVal DATETIME," & _
"CONSTRAINT PrimaryKey PRIMARY KEY (DateVal))"

dbs.Execute strSQL, dbFailOnError

' fill temporary table
For dtmDate = VBA.Date - 4 To VBA.Date + 10
strSQL = "INSERT INTO DateList(DateVal) " & _
"VALUES(#" & Format(dtmDate, "yyyy-mm-dd") & "#)"
dbs.Execute strSQL, dbFailOnError
Next dtmDate

strSQL = "SELECT * FROM DateList"

' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(conQDF, strSQL)

DoCmd.OpenQuery conQDF

' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name

End Sub

The temporary DateList able which it creates will remain extant after the
procedure is executed. It can't be deleted while the temporary query is open
as this locks the table, but you can delete it after closing the temporary
query if you wish.

Ken Sheridan
Stafford, England
 
That was what I was looking for. Thanks. Obviously it works great and the
previous values hold as well.
 
Jake said:
Can anyone tell me a way to create a list of dates by running a query
in access 2003. I don't want to create a table. I just want to run
a query and list 15 days sequentially. For instance I want my result
set to be 4 days prior to today, today and the next 10 days.
Click to expand...

I don't know why you don't like Allen's solution of using a counting table,
but here's an alternative:

You can use a union query. As Allen says, the records have to come from
somewhere. Unlike SQL Server, Jet does no allow you to do a simple "SELECT
somevalue" without a FROM clause.
Many people create a single-record table for this purpose. It would look
like this:

select Date()-4 As Dates from tblSingleRecord
union all
select Date()-3 from tblSingleRecord
union all
select Date()-2 from tblSingleRecord
etc.

Personally, I would rather use the counting table, which will be useful for
other things besides this.
 
Back
Top