Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Modules & VBA (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12)
-   -   [SOLVED] List dates between date range (https://www.access-programmers.co.uk/forums/showthread.php?t=305359)

lookforsmt 06-04-2019 09:35 AM

List dates between date range
 
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 06-04-2019 09:47 AM

Re: List dates between date range
 
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?

theDBguy 06-04-2019 09:55 AM

Re: List dates between date range
 
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 06-04-2019 10:24 AM

Re: List dates between date range
 
1 Attachment(s)
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

theDBguy 06-04-2019 10:48 AM

Re: List dates between date range
 
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 06-04-2019 11:05 AM

Re: List dates between date range
 
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

Quote:

Argument not optional
any suggestions anyone pls

theDBguy 06-04-2019 11:07 AM

Re: List dates between date range
 
Quote:

Originally Posted by lookforsmt (Post 1625968)
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 06-04-2019 11:15 AM

Re: List dates between date range
 
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 06-04-2019 11:31 AM

Re: List dates between date range
 
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 06-04-2019 11:41 AM

Re: List dates between date range
 
1 Attachment(s)
As it is not

lookforsmt 06-04-2019 11:52 AM

Re: List dates between date range
 
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 06-04-2019 12:01 PM

Re: List dates between date range
 
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 06-04-2019 12:05 PM

Re: List dates between date range
 
got it, thanks Gasman. I wiill close the thread as Solved

theDBguy 06-04-2019 12:13 PM

Re: List dates between date range
 
Quote:

Originally Posted by lookforsmt (Post 1625984)
got it, thanks Gasman. I wiill close the thread as Solved

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Gasman 06-04-2019 12:14 PM

Re: List dates between date range
 
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?


All times are GMT -8. The time now is 12:21 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World