How many Thursdays in current month

[tbjmobile@gmail.com]

Hi,
Can anyone tell me how I can find the integer of how many Thursdays there are in any current month please?
I've got as far as;
=DateDiff("w",DateSerial(Year(Date()),Month(Date() ),1)-1,DateSerial(Year(Date()),Month(Date())+1,1)-1,5)


But changing the system month, it fails at March 2018 giving 4 weeks. I've tried enclosing a -1 in ()s in the first Dateseial part because Datediff doesn't count the first day but, from there, I'm lost.
Thanks,
Trevor.

[ranman256]

send the function a date (or today)
usage:
msgbox getThursdayCount(Date)

Function getThursdayCount(byval pvDate)
Dim vDat
Dim i As Integer


vDat = Month(pvDate) & "/1/" & Year(pvDate)
While Format(vDat, "w") <> vbThursday
   vDat = DateAdd("d", 1, vDat)
Wend


While Month(vDat) = Month(pvDate)
   i = i + 1
   vDat = DateAdd("d", 7, vDat)
Wend
getThursdayCount = i
End Function

[orange]

Here is another

'---------------------------------------------------------------------------------------
' Procedure : NumSpecificDayInMonth
' Author    : mellon
' Date      : 08-Feb-2018 (from google search)
' Purpose   : To get the number of specific weekdays in a month
'
'  datThis starting day of the Month in question eg 1-Feb-2018  is #2/1/2018#
'  intDay  is the weekday you want to count  eg 1-sun 2-Mon.....7-Sat (defaults to MOnday)
'---------------------------------------------------------------------------------------
'Public Function NumSpecificDayInMonth(ByVal datThis As Date, _
                            Optional ByVal intDay As Integer = vbMonday) As Integer
                            
                            
10  datThis = DateAdd("d", 1 - Day(datThis), datThis)
20  intDay = (intDay Mod 7) + 1
30  intDay = WeekDay(datThis, intDay) - 1
40  NumSpecificDayInMonth = DateDiff("d", datThis, DateAdd("m", 1, datThis))
50  NumSpecificDayInMonth = (NumSpecificDayInMonth + intDay) \ 7
End Function

Test for Feb 2018

?NumSpecificDayInMonth(#2/1/2018#,5) '5 is Thursday (based on Sun=1, Mon=2.....)

4

[tbjmobile@gmail.com]

Thank you both for your responses. I've not attempted any vba yet but I guess that it's the best way for this one so I've started this afternoon.
Trevor.

[tbjmobile@gmail.com]

Hello,
I've done some studying of vab in this code and I'm now pretty sure of how the code works but I have two questions;
1) If my system date is European e.g. 15/03/2018 do I need to change anything (maybe
2) I want to use the result integer 3 times in my report so how would I do that, how would I get that value (getthursdaycount or numspecificdayinmonth or whatever) into a textbox that is on my report to get that value?
Thanks.

[tbjmobile@gmail.com]

I forgot to say that I want to run the procedure on reportLoad so that the number of Thursdays in Month is already in the form and being used to calculate weekly costs.

[ssanfu]

1) If my system date is European e.g. 15/03/2018 do I need to change anything (maybe

I would have to say "Yes", you will have to convert the date to American format, based on the info from Allen Browne at
http://www.allenbrowne.com/ser-30.html
Look for "Dates in Strings" at the bottom of the page.

After testing, I had to modify the SQLDate function.

So the function to convert European date format to American format is

Function fSQLDate(vDate As Variant) As Date
    If IsDate(vDate) Then
        fSQLDate = CDate(Format(vDate, "mm\/dd\/yyyy"))
    End If
End Function

NOTE: I added a prefix "f" (now fSQLDate) to distinguish this function from the original.

Example usage

?NumSpecificDayInMonth(fSQLDate(#15/3/2018#),5)

-----------------------------------------------------------------------

2) I want to use the result integer 3 times in my report so how would I do that, how would I get that value (getthursdaycount or numspecificdayinmonth or whatever) into a textbox that is on my report to get that value?

Not sure I understand what you are asking, but there is nothing stopping you from referencing the form control (getthursdaycount or numspecificdayinmonth or whatever) multiple times in a report.

[tbjmobile@gmail.com]

Hi Steve and thanks for responding again. Understood re. the date and thank yoiu for the code.
Regarding the other question, although I've been trying with this for 3 days and using all the online resources I can find, they don't seem to have helped me. Your word referencing is a perfect one. I want to have the number of Thursdays in the month, appear on the report straight away when it opens. Access is giving me Private Sub Report_Load() and as I want a return I suppose I must change that to a function not a sub, and As Integer. At the moment I've been filling in the number in a field in the table each month, but I've seen that I shouldn't use table field names on the report so it will have to be a textbox, namely numspecificdayinmonth. Is this unbound and how do I reference it from the vba? I have the [Event procedure] and click through to the code window. Access has already made a Report_rptAccount item in my Microsoft Access Class Objects folder. What I have failed to get right so far is to get the numspecificdayinmonth.value into it's textbox or any other property on the report. I know that I am trying to run before I can walk but this will be just one database for my daughter's business so I won't have the time to learn everything right the way through, to only use a small part of Access' power, once.
Another thing which is puzzling me is that when I compile what code I have tried, I don't get any error message, just a message box asking if I want to save the Macro ??
Thanks again.

[Minty]

In your reports load event simple set
Me.YourUnboundTextBox = NumSpecificDayInMonth(fSQLDate(#15/3/2018#),5)

Or have I missed something ?

[tbjmobile@gmail.com]

Hello Minty,
My original question was to ask for some code to find the amount of Thursdays in any month (from system date). Guys have kindly sent me code but I want it to run on report load and it's that that I'm stuck with. Everything I try compiles alright but doesn't fill my field from the table, or a textbox.
Regards,
Trevor.

[ssanfu]

but I want it to run on report load and it's that that I'm stuck with.

Why are you stuck with/ Why do you want to execute the function from the report load event? I think I have only ever used report events 2 or 3 times.

What is the record source of the report? A table or a query?

I (almost) always use queries as the record sources for forms/reports. In the query , you can have a column for the "TotalDaysInMonth". When the report starts to open, the query is executes, the report controls are populated.

Maybe I am missing something...........

[orange]

How and where exactly did you want to use this value?
Perhaps an example would help.

[tbjmobile@gmail.com]

LPS 07032018 1017.zipHello again All,
This is my database so far. There is no real data in it. The rptAccount will only be accessed/used once per month so I wanted to calculate the Thursdays in the month on/before opening it, so that we didn't forget to do so and lose money by only charging for 4 weeks instead of 5. Why Thursdays? Historically flights only arrived here on Thursdays from the UK, our principal visitors, so regular weekly charges are based on how many Thursdays are in the month. ChgWks is a different concept, it is dependent on how long the visitor stays and what level of service is required called LimpCode. There are 4 queries to run before the report is opened and I know I can later reduce the amount of them. I hope this explains better the requirement and concept. As I have said earlier in the thread, I have tried many times to place the calculated value from the vba into the report with no success yet, I either get prompts for Thursdays or nothing at all. At the moment the report is using the tblAccount field called ThursInMonth to calculate the other controls.
Thanks again,
Trevor.

[Minty]

Sorry I can't open that I'm using Access 2010.
It appears though as you are storing the value for Thursdays in the month in a table? That should be unnecessary if you can calculate it.

[tbjmobile@gmail.com]

Hi Minty,
I've tried to save as compatible with 2003 or 2007 but can't because it apparently uses non-compatible things. I know I'm using a table field at the moment and I did refer to this in the post. It's so that folk can see what I want when my calculation problem is solved.
Thanks for looking though.