Generate a date sequence, or express a time interval, in Access 20

[Alasdair Fraser]

I'm working in Access 2002/3. I want to avoid writing out a sequnece of
dates... I want a piece of VBA code which sequences all the dates, between
two given dates.

For example, putting in a Admission Date 23-mar-2003, and a Discharge Date
23-apr-2007, produces over 1400 numbers which I don't want to type.

The reason I need this sequence is to discover how many clients/patients
were in the hospice at the start and end of each month.

Currently this is a historical database, which is why I have a defined
start/end date. Ideally the resulting data would be copied to a subdatasheet
on each Prescription table.

Even better, is there any way to express a time period, which can be
queried, instead of a single date? I'm no expert and I can't write code.

Thanks in anticipation...
If this is too boring to answer, please refer me to somewhere which does.
For free.

 

[Matt]

I'm working in Access 2002/3. I want to avoid writing out a sequnece of
dates... I want a piece of VBA code which sequences all the dates, between
two given dates.

For example, putting in a Admission Date 23-mar-2003, and a Discharge Date
23-apr-2007, produces over 1400 numbers which I don't want to type.

The reason I need this sequence is to discover how many clients/patients
were in the hospice at the start and end of each month.

Currently this is a historical database, which is why I have a defined
start/end date. Ideally the resulting data would be copied to a subdatasheet
on each Prescription table.

Even better, is there any way to express a time period, which can be
queried, instead of a single date? I'm no expert and I can't write code.

Thanks in anticipation...
If this is too boring to answer, please refer me to somewhere which does.
For free.

the following code will loop through each day one by one

Sub loopDays(fromDate as date, toDate as date)
dim dte as Date

For Each dte = fromDate to toDate
MsgBox dte
Next dte
End Sub


If the fromDate is 1/1/2007 and the toDate is 1/3/2007 this code will
display 3 message boxes, one for 1/1, one for 1/2, one for 1/3.

Put any code that you need based on date in place of the MsgBox code.

 

[John W. Vinson]

Even better, is there any way to express a time period, which can be
queried, instead of a single date? I'm no expert and I can't write code.

No code is needed whatsoever. To find all patients who were in care as of a
certain date, you can use a Query. Include whatever fields you want to see; as
a criterion on StartDate use

<= [Enter date:]

and on EndDate use

= [Enter date:] OR IS NULL

to find all patients between these dates, or patients who are still in care
(no end date).

If you want a report listing the patients in care as of the end of each month
for a range of dates, just create a little table with one datefield; manually
or using Excel, fill in the first day of each month for whateve range you
want. You can then create a Query including both these tables, with NO join
line; instead put the above criteria using the datefield in this table rather
than [Enter date:]

It is almost certainly not necessary nor appropriate to fill in every day in
the range.

John W. Vinson [MVP]

 

[Michael Gramelspacher]

I'm working in Access 2002/3. I want to avoid writing out a sequnece of
dates... I want a piece of VBA code which sequences all the dates, between
two given dates.

For example, putting in a Admission Date 23-mar-2003, and a Discharge Date
23-apr-2007, produces over 1400 numbers which I don't want to type.

The reason I need this sequence is to discover how many clients/patients
were in the hospice at the start and end of each month.

Currently this is a historical database, which is why I have a defined
start/end date. Ideally the resulting data would be copied to a subdatasheet
on each Prescription table.

Even better, is there any way to express a time period, which can be
queried, instead of a single date? I'm no expert and I can't write code.

Thanks in anticipation...
If this is too boring to answer, please refer me to somewhere which does.
For free.

In conjunction with John Vinson's example, this fills an end-of-month
calendar table named Calendar. There is only one column, calendar_date
datetime not null primary key.

Sub MakeEOMCalendar()

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim d As Date

Set db = DBEngine(0)(0)

Set rst = db.OpenRecordset("Calendar", dbOpenTable, dbAppendOnly)

On Error Resume Next
For d = #1/1/2003# To #12/31/2009#

' if this date is equal to its end-of-month date
If d = DateAdd("m", DateDiff("m", 1, d), 1) Then
' .. then add date to calendar table
rst.AddNew
rst("calendar_date") = d
rst.Update
End If
Next d

End Sub

 

[Alasdair Fraser]

Thanks all.