I have a financial DB which contains an option to produce repeating payments by month. This is all working fine.
I would now like to do the same for repeating weekly payments, but I can't figure our how to a) establish the number of days in any specified month and b) how to work out when February has 28 days and 29 in leap years.
Can anyone give me a pointer, please?
Thanks
Silverback
Days in a month
-
- 2StarLounger
- Posts: 120
- Joined: 22 Feb 2022, 09:04
Re: Days in a month
You could use the DateSerial function with zero day.?
That gives you the last day of the previous month?
You could also use DateAdd() function to add the days, then you would not have to worry about leap years?
That gives you the last day of the previous month?
Code: Select all
? DateSerial(2022,8,0)
31/07/2022
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Days in a month
Using the same idea: given a date in a variable or field or control d
Day(DateSerial(Year(d), Month(d)+1, 0))
returns the number of days in the month of date d.
Day(DateSerial(Year(d), Month(d)+1, 0))
returns the number of days in the month of date d.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Days in a month
Many thanks, both. DateSerial seems to be a very impressive and flexible function.
Silverback
1750 : Routine written and working as required. Thanks again, both.
Silverback
1750 : Routine written and working as required. Thanks again, both.
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Days in a month
I spoke too soon.
There is a glitch in the VBA. On 1st running, it stops at the first line of executable code. If I press F5 (Run) it continues and executes without error and produces exactly the records I want. On 2nd and subsequent runnings, it does not stop and just runs to completion.
I did once have a stop on the first line when I was developing the code, but I have removed that, now.
I am baffled. Any ideas, please?
Thanks
Silverback
There is a glitch in the VBA. On 1st running, it stops at the first line of executable code. If I press F5 (Run) it continues and executes without error and produces exactly the records I want. On 2nd and subsequent runnings, it does not stop and just runs to completion.
I did once have a stop on the first line when I was developing the code, but I have removed that, now.
I am baffled. Any ideas, please?
Thanks
Silverback
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Days in a month
Does the problem go away if you select Database Tools > Compact and Repair Database?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Days in a month
Unfortunately, no.
Silverback
Silverback
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Days in a month
What happens if you delete that line, then type it again from scratch?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Days in a month
Answer. It works.
Cut the first line from the VBA, saved the form and ran it. Everything OK.
Edited the VBA again by pasting the first line back in again and saved the form. Now everything runs OK. Duh
Many thanks
Silverback
Cut the first line from the VBA, saved the form and ran it. Everything OK.
Edited the VBA again by pasting the first line back in again and saved the form. Now everything runs OK. Duh
Many thanks
Silverback