List dates between date range (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
HI!

I am trying to list the dates between the start date and end date to avoid typing the dates one by one in the table.

Below is the code i have found and works pretty well in the immediate window.
However i am clueless how to call this function through a form.
My form has start and end date field.
a) The on-click button on form should trigger the code to run and list the between dates in the table, "tblDate"
b) Alternatively i should also be able to call the code through query

Code:
Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing

End Function
 

Micron

AWF VIP
Local time
Today, 14:40
Joined
Oct 20, 2018
Messages
3,476
hard to be certain if you're trying to append dates or return existing ones as the messages are mixed " trying to list the dates" and " to avoid typing the dates". I think it's append.
Your loop is only based on 2 existing date values and not some big range of dates because Access isn't going to manufacture date values for you (AFAIK). Possibly best way to add dates is via DateAdd function - but why not just use a query rather than a recordset?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 11:40
Joined
Oct 29, 2018
Messages
21,358
Hello. I love it when I learn something new everyday. I am so used to seeing things like For x = 1 To 100 and such and never realized using dates will also automatically increment in this situation. Very, very interesting. Thanks!
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thanks for the response.

Yes the data is append to the table. but it also helps me to avoid typing the in between dates

i have attached my db for clarity on what i am really want to do in my project.

I have form: my form is linked to table, "Table1" where i have start Date and end Date field. once i capture these dates and then hit on the on-click button on the form it should append the dates in the Table1.
I am sure if this is possible to be able to generate the dates on-click form

i just saved a revised file2 with the updated form
 

Attachments

  • TestDateSequence_2.accdb
    452 KB · Views: 116

theDBguy

I’m here to help
Staff member
Local time
Today, 11:40
Joined
Oct 29, 2018
Messages
21,358
Hi. Just as a point of possible future reference, here's an article I wrote a while back showing how to generate a series of dates. You might be able to apply something similar, if the code approach doesn't quite work out.
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thanks DBguy for the article shared. i will try this another time.

just trying to modify the code on the form. Currently i am getting an error

Argument not optional

any suggestions anyone pls
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:40
Joined
Oct 29, 2018
Messages
21,358
Thanks DBguy for the article shared. i will try this another time.

just trying to modify the code on the form. Currently i am getting an error

any suggestions anyone pls
Hi. I couldn't download your file because I'm using my phone. If you're getting an "argument not optional" error, then it means you didn't supply the required arguments to the function. For example, maybe you didn't pass the value for the start or end date or both.
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
honestly my vba knowledge is limited, i am trying to google the solution but not able to find one. Well if you happen to be near to your laptop/pc in future then do help if possible.

thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,046
If the code is in the form you can use the Me.StartDate and Me,EndDate or set your dates variables from those controls.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,046
As it is not
 

Attachments

  • TestDateSequence_2.accdb
    700 KB · Views: 137

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
thanks Gasman for the code. I want to know the date variables that you mentioned. Is it different than this?

another help which i wanted is to call the function from the query. how do i do that
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,046
Well you had the code in it's own module.
You had parameters for the function. I did change it to a Sub as it was not returning anything.
You were calling the function, but you were not passing the parameters.?

If that is the only place you will use the query, put the form controls in the query. use the Build button to get the correct syntax to refer to the controls.
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
got it, thanks Gasman. I wiill close the thread as Solved
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,046
I do not think you can do it with a query as you have coded it.?
Normally there would be the dates themselves in the table and then you specify the start and end.
As it stands now, you just have the range of dates in the table you are trying to append from.?

Code:
INSERT INTO tblDate ( TheDate )
SELECT tblDate.TheDate
FROM tblDate, Table1
WHERE (((tblDate.TheDate) Between [Table1].[StartDate] And [Table1].[EndDate]));

The experts will know a better way. In fact didn't TheDBguy offer a method?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,046
arnelgp offered a method a good while back that I used.
You have 3 tables, for day, month,year. Day is 1 to 31, Month 1 to 12, Years, whatever years you want.

I have a form like you that has start and enddate (unbound) and a button that runs the query code below

Code:
INSERT INTO tblDates ( DayDate )
SELECT DateSerial([YearNo],[MonthNo],[DayNo]) AS Dates
FROM tblDay, tblMonth, tblYear
WHERE (((DateSerial([YearNo],[MonthNo],[DayNo])) Between [Forms]![frmGenerateDates]![txtStartdate] And [Forms]![frmGenerateDates]![txtEnddate]) AND ((IsDate([MonthNo] & "/" & [DayNo] & "/" & [YearNo]))<>False))
ORDER BY DateSerial([YearNo],[MonthNo],[DayNo]);

Works like TheDBguy's method I believe, that is with a Cartesian join.?

Perhaps you can tailor this. ?

HTH
 

lookforsmt

Registered User.
Local time
Today, 22:40
Joined
Dec 26, 2011
Messages
672
Thanks Gasman, would try that too.

Thank you all for all the support and advices
 

Users who are viewing this thread

Top Bottom