Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-04-2019, 09:35 AM   #1
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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

lookforsmt is offline   Reply With Quote
The Following User Says Thank You to lookforsmt For This Useful Post:
theDBguy (06-04-2019)
Old 06-04-2019, 09:47 AM   #2
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
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?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 06-04-2019 at 09:48 AM. Reason: clarification
Micron is offline   Reply With Quote
Old 06-04-2019, 09:55 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,034 Times in 1,015 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-04-2019, 10:24 AM   #4
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: List dates between date range

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
Attached Files
File Type: accdb TestDateSequence_2.accdb (452.0 KB, 6 views)
lookforsmt is offline   Reply With Quote
Old 06-04-2019, 10:48 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,034 Times in 1,015 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-04-2019, 11:05 AM   #6
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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
lookforsmt is offline   Reply With Quote
Old 06-04-2019, 11:07 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,034 Times in 1,015 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: List dates between date range

Quote:
Originally Posted by lookforsmt View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-04-2019, 11:15 AM   #8
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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
lookforsmt is offline   Reply With Quote
Old 06-04-2019, 11:31 AM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,070
Thanks: 421
Thanked 749 Times in 727 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-04-2019, 11:41 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,070
Thanks: 421
Thanked 749 Times in 727 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: List dates between date range

As it is not
Attached Files
File Type: accdb TestDateSequence_2.accdb (700.0 KB, 7 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-04-2019, 11:52 AM   #11
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
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
lookforsmt is offline   Reply With Quote
Old 06-04-2019, 12:01 PM   #12
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,070
Thanks: 421
Thanked 749 Times in 727 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 06-04-2019, 12:05 PM   #13
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 514
Thanks: 74
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: List dates between date range

got it, thanks Gasman. I wiill close the thread as Solved
lookforsmt is offline   Reply With Quote
Old 06-04-2019, 12:13 PM   #14
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,467
Thanks: 50
Thanked 1,034 Times in 1,015 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: List dates between date range

Quote:
Originally Posted by lookforsmt View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 06-04-2019, 12:14 PM   #15
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,070
Thanks: 421
Thanked 749 Times in 727 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter by date range for any one of three dates. manderson_zim Modules & VBA 5 04-05-2011 07:00 AM
Show dates between a date range billwest Queries 0 12-09-2009 01:23 AM
Report Date Range showing all dates thunderbolt1164 Reports 29 03-15-2008 02:59 AM
It is possible to list all dates inbetween a date range? Please help joe789 Queries 3 08-20-2004 11:45 AM
Matching a date with a range of dates jvanderw Modules & VBA 1 01-24-2002 06:14 AM




All times are GMT -8. The time now is 12:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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