In Ms Access (VBA) , How to get all the dates of the month after selecting the certain month and weekdays? (1 Viewer)

[anandan.cr@gmail.com]

In Ms Access (VBA) , How to get all the dates of the month after selecting the certain month and weekdays?

 

[561414]

Are you creating a calendar? Your explanation is vague.

"Get all the dates of the month" means how many days there are? If so

Dim FirstDay As Date
FirstDay = DateSerial(Year(YourDate),Month(YourDate),1)

Dim LastDay As Date
'The 0 as day returns the last day of the previous month
LastDay = DateSerial(Year(YourDate),Month(YourDate)+1,0)

Dim DaysOfMonth as long
DaysOfMonth = LastDay - FirstDay + 1

This approach only needs a date. It does the rest of the math for you. You need to provide the year, because there are leap years, so February has a different amount of days in different years.

You could also do something like this to get the amount of days in current month
Day(DateSerial(Year(Now), Month(Now) + 1, 1) - 1) - Day(DateSerial(Year(Now), Month(Now), 1)) + 1

None of these is tested.

If you want to get the weekday, use the built in WeekDay function with a dateserial to get whatever day of the month you need.

 

[anandan.cr@gmail.com]

Dear 561414
How to get all the dates of the month after selecting the certain month and weekdays? VBA-Access:

Selecting the weekdays
"Combo box" Select: Weekdays (Eg: Wednesday)

Input the month
"TextBox" (Input Receive Eg : FEB-2023 (MMM-YYYY))

RESULT Get all the dates : "List Box" Display : Eg : 1,8,15,22

 

[Josef P.]

1. Find the first Wednesday (for example)
=>

1a) check weekday of 1. of Month ... x = weekday(FirstDayOfMonthDate)​

1b) calc the next Wednesday ... nextWednesday = dateadd(....)​

2. Loop Until end of month (+7)

 

[561414]

Oh, that is more interesting. You have to account for the way your data is input. There are many things you should take into account here, such as:
How are the week days considered data wise? numbers or strings?
How about months?
Are you going to always type month-year? like this? FEB-2023, or can it be february-2023? or something else?

Only after you know, can you actually build it.

Take a look at the attached

 

[arnelgp]

see this demo (Form1).
also see the tables and queries.

 

[anandan.cr@gmail.com]

1. Find the first Wednesday (for example)
=>

1a) check weekday of 1. of Month ... x = weekday(FirstDayOfMonthDate)​

1b) calc the next Wednesday ... nextWednesday = dateadd(....)​

2. Loop Until end of month (+7)

Requesting you to please provide me VBA Code

 

[Josef P.]

Let Me Google That

For all those people that find it more convenient to bother you with their question than to google it for themselves.

letmegooglethat.com

Note: Another simple method would be to use a calendar table. (But then this is no longer pure VBA.)