Date range question

[jeo]

On my form I have a start date and an end date (form is based on a query which contains a DateID as PK and 2 date variables).
Let’s say the user picks 1/1/05 as start date and 1/10/05 as end date.
Right now everything is stored as a range of these dates 1/1/05-1/10/05.
What I’m wondering if there is a way to automatically fill in start dates 1/1/05, 1/2/05, 1/3/05, 1/4/05, etc and the same thing with end dates.
This way the user can pick a range, rather than having to enter dates for each day, but each day will be accounted for.
Any suggestions, ideas. May be this is not even doable.
Any help will be greatly appreciated. Thanks.

 

[ColinEssex]

You are using the criteria

"Between Forms!YourFormName![StartDate] and Forms!YourFormName![EndDate]"

in the query are you?

Col

 

[jeo]

Right now I'm not using any criterias at all.
But what I want to happen is somehow the date range to be automatically populated day by day, rather than day range.
What would this criteria: "Between Forms!YourFormName![StartDate] and Forms!YourFormName![EndDate]" do, where do I need to put this criteria at and and do I need to add any other variables to my table to store those values?

Thanks!

 

[ColinEssex]

I see now that you want the "InBetween" dates of a start date and and end date to be displayed.

You'll need to have a 2nd table to post the data to. Then you'll need some VB code to work out the dates. You need a "For Next Loop" - along the lines of (this is very rough mind you just to give you an idea)

For x = StartDate to EndDate

'Post Data to newTable

next x

Loop

Have a search in the forum for "Dates in between" or something similar because this subject crops up from time to time.

Failing that, post back and I'll try to help out, I have to rush off soon and will get back to you tomorrow

Col

 

[jeo]

Thank you, I will do that.

 

[jeo]

found something, but need help

Col,
Thank you for all your replies.
I did find something here:

http://www.access-programmers.co.uk/forums/showthread.php?t=14065&highlight=InBetween

Posted by Harry a while ago. I'm pretty sure this is the code you were reffering to.
I added a button, for now, on the form and changed Harry's code around, but some of the code is giving me trouble - that ";" isn't cooperating.
I'm not much of a programmer , so I'm not sure why it's giving me an error.
I hope that you (or someone) could help me out and let me know what I need to change in order to make this code work.
Thank you for all your help!

 

[jeo]

OK...
I partially figured it out and some of the code is working, however I can't figure out how to make it stop looping.
Not sure what I need to change. It does add all the dates I need, it just keeps going and adds them...
Hope someone could help me figure this one out.
Thanks.
Here is the code:
Dim rstOrig As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim SDate, EDate, TestDate As Date

Set rstOrig = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)
Set rstNew = CurrentDb.OpenRecordset("tblDatesNew", dbOpenDynaset)
rstOrig.MoveFirst
Do
SDate = rstOrig("Start")
EDate = rstOrig("End")
TestDate = SDate
Do
rstNew.AddNew
rstNew("DateID") = rstOrig("DateID")
rstNew("Start") = TestDate
rstNew.Update
TestDate = TestDate + 1
Loop Until TestDate > EDate
If IsNull(EDate) Then
NextLoop:
rstOrig.MoveNext
End If
Loop Until rstOrig.EOF = True
rstOrig.Close
rstNew.Close
End Sub

 

[Brianwarnock]

Your test for NULL is in the wrong place

rstOrig.MoveFirst
Do
SDate = rstOrig("Start")
EDate = rstOrig("End")
If IsNull(EDate) Then
GoTo NextLoop
End If
TestDate = SDate
Do
rstNew.AddNew
rstNew("DateID") = rstOrig("DateID")
rstNew("Start") = TestDate
rstNew.Update
TestDate = TestDate + 1
Loop Until TestDate > EDate
[I]NextLoop:[/I]
rstOrig.MoveNext
Loop Until rstOrig.EOF = True
rstOrig.Close
rstNew.Close

Brian

 

[jeo]

working, but...not correctly

Thanks for posting a reply.
This worked, but...
I have 2 different records there one 1/1/05-1/10/05 and the second one
1/15/05-2/15/05
This code produced the dates from 1/1/05 - 2/15/05 and what I wanted to happen is to produce those dates depending on that record.
So if I'm on the 1/1/05-1/10/05 record and I push that button, then only dates for this record would be posted to the table, not all of them.
Is this doable at all?
I know then when the code looped, it looped only for that one record.
Thank you very much for all your help!

 

[jeo]

Actually, never mind...
This works because DateID is different in the new table.
Sorry...
Thank you for all your help!

 

[Brianwarnock]

I'm puzzled as I have had another message from you saying that it didn't work and if I read your posts correctly you only want to select a particular DateID, which is not visible on your form, I have had a quick play with the code and I think the code below does what you appear to want.

Private Sub Command3_Click()

Dim rstOrig As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim SDate, EDate, TestDate As Date


Set rstOrig = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)
Set rstNew = CurrentDb.OpenRecordset("tblDatesNew", dbOpenDynaset)

rstOrig.MoveFirst


checkDateID:
If rstOrig("DateID") <> Me.DateID Then
rstOrig.MoveNext
GoTo checkDateID
End If
SDate = rstOrig("Start")
EDate = rstOrig("End")
If IsNull(EDate) Then
GoTo Quit
End If
TestDate = SDate
Do
rstNew.AddNew
rstNew("DateID") = rstOrig("DateID")
rstNew("Start") = TestDate

rstNew.Update
TestDate = TestDate + 1
Loop Until TestDate > EDate

Quit:
rstOrig.Close
rstNew.Close
End Sub

Oh yes I changed the DateID on the tblDatesNew to number Long integer and allowed duplicates.

 

[jeo]

No, I did have a problem, but I deleted that message because I thought i was getting to be a big pain.
Thank you for posting this.
I made the change and tried to run the code without any data in the tables and I get stuck on rstOrig.MoveFirst
it says No current Record.
Any ideas?
Thanks.

 

[jeo]

I figured that one out...
I just had to save current record before that code ran. Once I did that, the error has gone away and it works great! Thank you.
However, I do have another thing that I need help with. I thought that users could come in and change the start/end dates on that particular record. Is there a way to delete already allocated dates for that DateID out of the table and replace them with the ones that a user changed?
Thanks.
I appreciate all the help!

 

[Brianwarnock]

Hi
I'm at home now being a part timer I don't work Thursdays and Fridays but the weather is lousy and so I logged onto my wife's computer and took a peek , thus I don't have access to the database, but my thinking on the problem would be to switch to using the info from the form rather than the original table, as I did to check the DateID.

ie SDate = Me.Start or whatever the name of the control on the form is

Hope this helps

Brian

 

[jeo]

That helps as well...
Thank you.
But could you steer me in the right direction for deleting date ranges as well?
I'm assuming that part of the code to add the dates to a new table will work for deleting them as well.
I just need some help on what I need to change.
Thanks again for all your help and I hope that the weather gets better soon.
I can't wait for spring and summer to get here finally.

 

[Brianwarnock]

Hi again

Thinking about it if you used the info from the form then your code would look like this, with correct form control names used

Private Sub Command3_Click()

Dim rstNew As DAO.Recordset
Dim SDate, EDate, TestDate As Date

Set rstNew = CurrentDb.OpenRecordset("tblDatesNew", dbOpenDynaset)

SDate = Me.StartDate
EDate = Me.Enddate
TestDate = SDate
Do
rstNew.AddNew
rstNew("DateID") = Me.DateID
rstNew("Start") = TestDate

rstNew.Update
TestDate = TestDate + 1
Loop Until TestDate > EDate

Quit:
rstNew.Close
End Sub

You may want to validate the dates on the form and I can't remember off hand the way to do that

Brian