List dates between date range

L

lookforsmt

Registered User.
Local time
Today, 15:11
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
 
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 by a moderator:
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!
 
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

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.
 
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
Click to expand...

any suggestions anyone pls
 
lookforsmt said:
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
Click to expand...
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.
 
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
 
If the code is in the form you can use the Me.StartDate and Me,EndDate or set your dates variables from those controls.?
 
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
 
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.
 
got it, thanks Gasman. I wiill close the thread as Solved
 
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?
 
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
 
Thanks Gasman, would try that too.

Thank you all for all the support and advices
 
You must log in or register to reply here.

Similar threads

D
Calendar displaying autonumber from a combo box..
Replies
5
Views
222
Pat Hartman
P
klsblues
VBA Conditional Formatting error because I have more than 3 formatting requirements.
2 3
Replies
53
Views
1,050
AHeyne
A
J
Code to change Date in a field if the checkbox is checked
Replies
5
Views
177
LarryE
LarryE
C
Optional Variant in Inventory Control code
Replies
7
Views
438
gemma-the-husky
gemma-the-husky
A
Match two numbers and then update
Replies
10
Views
264
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom