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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alasdair Fraser - 07 May 2007 13:48 GMT

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.

Signature

UK Volunteer Constructing hospice database in India using Access 2003 for
the 1st time. Help.

Reply to this Message

Matt - 07 May 2007 15:30 GMT

On May 7, 8:48 am, Alasdair Fraser
<fraser.alasd...@gmail.com(nospmpls)> wrote:

> 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
[quoted text clipped - 19 lines]
> UK Volunteer Constructing hospice database in India using Access 2003 for
> the 1st time. Help.

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.

Reply to this Message

John W. Vinson - 07 May 2007 20:37 GMT

>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]

Reply to this Message

Alasdair Fraser - 17 May 2007 18:34 GMT

Thanks all.

Reply to this Message

Michael Gramelspacher - 08 May 2007 02:05 GMT

> 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
[quoted text clipped - 16 lines]
> 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

Reply to this Message