Days in a month

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Days in a month

Post by silverback »

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

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Days in a month

Post by Gasman »

You could use the DateSerial function with zero day.?
That gives you the last day of the previous month?

Code: Select all

? DateSerial(2022,8,0)
31/07/2022 
You could also use DateAdd() function to add the days, then you would not have to worry about leap years?
Using Access 2007.
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.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Days in a month

Post by HansV »

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.
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Days in a month

Post by silverback »

Many thanks, both. DateSerial seems to be a very impressive and flexible function.
Silverback

1750 : Routine written and working as required. Thanks again, both. :thankyou:

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Days in a month

Post by silverback »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Days in a month

Post by HansV »

Does the problem go away if you select Database Tools > Compact and Repair Database?
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Days in a month

Post by silverback »

Unfortunately, no.
Silverback

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Days in a month

Post by HansV »

What happens if you delete that line, then type it again from scratch?
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Days in a month

Post by silverback »

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:exclamation:
Many thanks
Silverback :thankyou: