Using a Date Range to create a list of dates

[homerj0216]

I would like to create a list of dates based on a date range.

If my date range is 03-01-08 to 03-10-08, I would like to create a query
that will show me the dates individually.
So, the new data set would be each date listed individually. (03-01, 03-02,
03-03, etc...)

Is this possible?

Thanks,
Mike

 

[Klatuu]

You are asking a How question with not nearly enough information to provide a
useable answer.
Perhaps if you would describe What you want to do, we can help with the How.

 

[homerj0216]

Dave,
I have a list of users, who each has a date range, for dates of service.
User 1 had service dates of 02-01 to 02-10
User 2 had service dates of 02-15 to 02-17
User 3 had service dates of 03-01 to 03-08

I would like to create a list, for each user, that shows the actual dates of
service for each user. So my new list would be:
user 1 02-01
user 1 02-02
user 1 02-03
user 1 02-04
user 1 02-05
user 1 02-06
user 1 02-07
etc...
user 2 02-15
user 2 02-16
user 2 02-17 (i would actually not like to count the last day of service)

I am trying to calculate days of service for date ranges. If a user had a
date range of 02-15 to 02-17, there total days of service would be 2. (we
don't count the last day).

I then need to count each date individually. Also, some users can have more
than one set of date ranges, so I need to check each set of date ranges
separately, and create a listing for each date range. (Then I need to check
if those date ranges are concurrent).

Thanks for the help

Mike

 

[Klatuu]

Where do you store the date ranges?
Where do you store the list of users?

We can get where you want to go, but we need to know some info so we can
offer a good suggestion.

 

[homerj0216]

Dave,
I would like to create a new table that houses the new list of dates with
users associated.

I am trying to get to a point where I can count the actual dates of service.
I need to add an incremental count to the dates of service.

so, for user 1, the table would yield:

user1 02/01 1
user1 02/02 2
user1 02/03 3
user1 02/04 4
etc...

where 1, 2, 3, 4 is the incremental count.
(also, where a user has multiple service date ranges, I need the system to
incrementally count all dates, so even if the date ranges are consecutive, it
still counts incrementally).

The table should show each user and each date of service, with an
incremental count for each date.

If we have to create a first table that shows users and date ranges, and
then incrementally count in another table/query that would fine.

Does that help?

Thanks,
Mike

 

[Klatuu]

I think you will need the two tables.
But, I don't believe you will be able to create the second table using a
query. I think you are going to have to use recordset processing in VBA.

 

[homerj0216]

Dave,
I am comfortable with that. If you can help with the programming that would
be greatly appreciated.

I am okay with however the train get to the station, as long as it gets there.

Mike

 

[Klatuu]

This is scrictly air code that has not been tested, but it should give you
the basic idea of how it is done. It may be the train is off the track and
has to be towed in

TableA
UserName
FromDate
ToDate

TableB
UserName
ServiceDate
DayCount

Dim rstUsers As Recordset
Dim rstService As Recordset
Dim lngDayCount As Long
Dim strCurrUser As String
Dim dtmServiceDate as Date

With Currentdb
Set rstUsers = .OpenRecordset("TableA")
Set rstService = .OpenRecordset("TableB")
End With

With rstUsers
.MoveLast
.MoveFirst
End With

Do While Not rstUsers.EOF
dtmServiceDate = rstUsers!FromDate
lngDayCount = 1
Do While dtmServiceDate < = rstUsers!ToDate
With rstService
.AddNew
!UserName = rsUsers!UserName
!ServiceDate = dtmServiceDate
!DayCount = lngDayCount
.Update
End With
dtmServiceDate = DateAdd("d",1,dtmServiceDate)
lngDayCount = lngDayCount + 1
Loop
rstUsers.MoveNext
Loop

rstUsers.Close
rstService.Close
Set rstUsers = Nothing
Set rstService = Nothing

 

[homerj0216]

Thanks Dave, I will check it out.

Even if the train rolls over and over into the station, hey, it got there
didn't it....

 

[Klatuu]

Even if the train rolls over and over into the station, hey, it got there

didn't it....

Yes, it did. <g>

Let me know how it goes.

 

[homerj0216]

Dave,
Well, something isn't right. After I copied and pasted the solution into a
module, some of my queries are not working any longer.

Before the data is ready for service date, I need to check the start date is
not before July 1, and the end date is not after Dec. 31.

I am using the DateSerial(y,m,d) function, but each time I run my query I
get a compile error on this formula. As long as I don't have the module in
the database, the query works fine. can you shed some light on this?

Also,
A couple of questions about the programming:

These beginning sections: They are just for reference to the tables, correct?

'TableA
'UserName
'FromDate
'ToDate

Here is the actual table name and structure I am using for TableA:
Databasebase Name: Quarterly Reports
Table name: SummaryofServiceDates
Fields:
Username
LastName
FirstName
DOB
BeginDate
EndDate

'TableB
'UserName
'ServiceDate
'DayCount

Dim rstUsers As Recordset
Dim rstService As Recordset
Dim lngDayCount As Long
Dim strCurrUser As String
Dim dtmServiceDate As Date

With CurrentDb - Should I change this name to [Quarterly Reports]
Set rstUsers = .OpenRecordset("TableA") - Should TableA be
represented as [Quarterly Reports].[SummaryofServiceDates]
Set rstService = .OpenRecordset("TableB")
End With

With rstUsers
.MoveLast
.MoveFirst
End With

Do While Not rstUsers.EOF
dtmServiceDate = rstUsers!FromDate
lngDayCount = 1
Do While dtmServiceDate <= rstUsers!ToDate
With rstService
.AddNew
!UserName = rsUsers!UserName
!ServiceDate = dtmServiceDate
!DayCount = lngDayCount
.Update
End With
dtmServiceDate = DateAdd("d", 1, dtmServiceDate)
lngDayCount = lngDayCount + 1
Loop
rstUsers.MoveNext
Loop

rstUsers.Close
rstService.Close
Set rstUsers = Nothing
Set rstService = Nothing

do I need to call this something? (like a function name, servicedates?)

I am not the greatest Module programmer. I can read it better than I can
write the code.

Thanks,
Mike

 

[homerj0216]

Dave,
Here is the code I am using. I set it up as a form button. But it says
object required.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


'TableA
'UserName
'FromDate
'ToDate

'TableB
'UserName
'ServiceDate
'DayCount

Dim rstUsers As Recordset
Dim rstService As Recordset
Dim lngDayCount As Long
Dim strCurrUser As String
Dim dtmServiceDate As Date


With CurrentDb
Set rstUsers = SummaryofServiceDates.OpenRecordset
Set rstService = DatesofService.OpenRecordset
End With

With rstUsers
SummaryofServiceDates.MoveLast
SummaryofServiceDates.MoveFirst
End With

Do While Not rstUsers.EOF
dtmServiceDate = rstUsers!EndDate
lngDayCount = 1
Do While dtmServiceDate <= rstUsers!BeginDate
With rstService
.AddNew
!UserName = rsUsers!Medicaid
!ServiceDate = dtmServiceDate
!DayCount = lngDayCount
.Update
End With
dtmServiceDate = DateAdd("d", 1, dtmServiceDate)
lngDayCount = lngDayCount + 1
Loop
rstUsers.MoveNext
Loop

rstUsers.Close
rstService.Close
Set rstUsers = Nothing
Set rstService = Nothing

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Can you help with this?

thanks,
Mike

Dave,
Well, something isn't right. After I copied and pasted the solution into a
module, some of my queries are not working any longer.

Before the data is ready for service date, I need to check the start date is
not before July 1, and the end date is not after Dec. 31.

I am using the DateSerial(y,m,d) function, but each time I run my query I
get a compile error on this formula. As long as I don't have the module in
the database, the query works fine. can you shed some light on this?

Also,
A couple of questions about the programming:

These beginning sections: They are just for reference to the tables, correct?

'TableA
'UserName
'FromDate
'ToDate

Here is the actual table name and structure I am using for TableA:
Databasebase Name: Quarterly Reports
Table name: SummaryofServiceDates
Fields:
Username
LastName
FirstName
DOB
BeginDate
EndDate

'TableB
'UserName
'ServiceDate
'DayCount

Dim rstUsers As Recordset
Dim rstService As Recordset
Dim lngDayCount As Long
Dim strCurrUser As String
Dim dtmServiceDate As Date

With CurrentDb - Should I change this name to [Quarterly Reports]
Set rstUsers = .OpenRecordset("TableA") - Should TableA be
represented as [Quarterly Reports].[SummaryofServiceDates]
Set rstService = .OpenRecordset("TableB")
End With

With rstUsers
.MoveLast
.MoveFirst
End With

Do While Not rstUsers.EOF
dtmServiceDate = rstUsers!FromDate
lngDayCount = 1
Do While dtmServiceDate <= rstUsers!ToDate
With rstService
.AddNew
!UserName = rsUsers!UserName
!ServiceDate = dtmServiceDate
!DayCount = lngDayCount
.Update
End With
dtmServiceDate = DateAdd("d", 1, dtmServiceDate)
lngDayCount = lngDayCount + 1
Loop
rstUsers.MoveNext
Loop

rstUsers.Close
rstService.Close
Set rstUsers = Nothing
Set rstService = Nothing

do I need to call this something? (like a function name, servicedates?)

I am not the greatest Module programmer. I can read it better than I can
write the code.

Thanks,
Mike

Yes, it did. <g>

Let me know how it goes.

 

[homerj0216]

Dave,
I got it working! It works perfectly. The train rolled into the station on
the tracks!

Thanks for the help.

Mike

Dave,
Here is the code I am using. I set it up as a form button. But it says
object required.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


'TableA
'UserName
'FromDate
'ToDate

'TableB
'UserName
'ServiceDate
'DayCount

Dim rstUsers As Recordset
Dim rstService As Recordset
Dim lngDayCount As Long
Dim strCurrUser As String
Dim dtmServiceDate As Date


With CurrentDb
Set rstUsers = SummaryofServiceDates.OpenRecordset
Set rstService = DatesofService.OpenRecordset
End With

With rstUsers
SummaryofServiceDates.MoveLast
SummaryofServiceDates.MoveFirst
End With

Do While Not rstUsers.EOF
dtmServiceDate = rstUsers!EndDate
lngDayCount = 1
Do While dtmServiceDate <= rstUsers!BeginDate
With rstService
.AddNew
!UserName = rsUsers!Medicaid
!ServiceDate = dtmServiceDate
!DayCount = lngDayCount
.Update
End With
dtmServiceDate = DateAdd("d", 1, dtmServiceDate)
lngDayCount = lngDayCount + 1
Loop
rstUsers.MoveNext
Loop

rstUsers.Close
rstService.Close
Set rstUsers = Nothing
Set rstService = Nothing

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Can you help with this?

thanks,
Mike

Dave,
Well, something isn't right. After I copied and pasted the solution into a
module, some of my queries are not working any longer.

Before the data is ready for service date, I need to check the start date is
not before July 1, and the end date is not after Dec. 31.

I am using the DateSerial(y,m,d) function, but each time I run my query I
get a compile error on this formula. As long as I don't have the module in
the database, the query works fine. can you shed some light on this?

Also,
A couple of questions about the programming:

These beginning sections: They are just for reference to the tables, correct?

'TableA
'UserName
'FromDate
'ToDate

Here is the actual table name and structure I am using for TableA:
Databasebase Name: Quarterly Reports
Table name: SummaryofServiceDates
Fields:
Username
LastName
FirstName
DOB
BeginDate
EndDate

'TableB
'UserName
'ServiceDate
'DayCount

Dim rstUsers As Recordset
Dim rstService As Recordset
Dim lngDayCount As Long
Dim strCurrUser As String
Dim dtmServiceDate As Date

With CurrentDb - Should I change this name to [Quarterly Reports]
Set rstUsers = .OpenRecordset("TableA") - Should TableA be
represented as [Quarterly Reports].[SummaryofServiceDates]
Set rstService = .OpenRecordset("TableB")
End With

With rstUsers
.MoveLast
.MoveFirst
End With

Do While Not rstUsers.EOF
dtmServiceDate = rstUsers!FromDate
lngDayCount = 1
Do While dtmServiceDate <= rstUsers!ToDate
With rstService
.AddNew
!UserName = rsUsers!UserName
!ServiceDate = dtmServiceDate
!DayCount = lngDayCount
.Update
End With
dtmServiceDate = DateAdd("d", 1, dtmServiceDate)
lngDayCount = lngDayCount + 1
Loop
rstUsers.MoveNext
Loop

rstUsers.Close
rstService.Close
Set rstUsers = Nothing
Set rstService = Nothing

do I need to call this something? (like a function name, servicedates?)

I am not the greatest Module programmer. I can read it better than I can
write the code.

Thanks,
Mike

Even if the train rolls over and over into the station, hey, it got there
didn't it....

Yes, it did. <g>

Let me know how it goes.