How many Thursdays in current month

[ssanfu]

The problem is with the table "tblAccount"; it has a field type that is incompatible with earlier versions of Access.


I moved all of the objects Execpt the table "tblAccounts" to a new dB, then re-created that table. I matched the field types with the table "tblAccountNext". I can now look at open all objects, but missing example data.
Maybe you can make the field types in "tblAccount" match the field types in "tblAccountNext" and then re-post the dB.......

The code to calculate the number of days in a month is not in the dB. The only code is behind the report and it is wrong.

And why to you have 2 tables with almost the exact fields? ("tblAccount" and "tblAccountNext")

Also, the relationship linking between the tables is all messed up.

[tbjmobile@gmail.com]

Hello Steve,
Shame that Access don't provide a save to 2010 and 2013 that can do without the few differences. Anyway, the code is on this thread, #2, kindly donated by ranman 256 and there is another, also kindly donated. I have looked high and low for the [Event procedure] syntax to run the 'On Load' procedure for the code and place the result into a textbox on the rptAccount. I explain in the thread why it has to be Thursdays and why I need it. I've found umpteen posts about On Click() and I'm doing an online VBA beginners course that doesn't cover it and there's no help on YouTube, they all seem to concentrate on other events. tblAccountNext is the same structure because at the end of each month there is an update query where I clear all the date out and replace balance brought forward with the value of tblAccount's final closing balance.
Now I know that there are other, cleaner ways of doing this and I will get around to them when I have everything working properly. For the moment though, it does all work, albeit clumsily but I don't know whether to use a function or sub for this Thursday count, or whether to use a function and a sub to trigger it. Neither do I know the syntax to get the value of the code into the report as a textbox On Load.
I hope this explains it.
Trevor.

[ssanfu]

Anyway, the code is on this thread, #2, kindly donated by ranman 256 and there is another, also kindly donated.

I used the code by orange (Post #3) and the SQLdate function that formats dates to international format by Allen Browne.

I have looked high and low for the [Event procedure] syntax to run the 'On Load' procedure for the code and place the result into a textbox on a form

Open a form in design view, then open the properties dialog window. Click on the EVENT tab. Click on the "On Load" event. Click on the ellipsis. Enter code. Tada!


I still think you are going to have problems because of the way you have set up relationships. It is not recommended to have text PK/FK fields.


Look at the on load event of the form.......

[Micron]

I recently upgraded (not by choice) so I will review all the posts and take a look if I find your db here.
EDIT
OK, I've looked at the report and
a) have no idea where you'd put the result of the calculation or whatever else you might do with it
b) the report is based on a table, which makes for very limited capability with respect to how you can present data
c) where or how you intend to provide the month...
On one of your forms (if so, which form and which control)?
A query parameter prompt (not a great idea)?
??

There's a whole bunch of other strange looking things going on, but that's OK - we are all noobs in the beginning
I could start by basing the report on a query based on the table, but I'm not sure how much 'altering' you're prepared to accept. Hate to do a bunch of work if you're just going to discard it.

[tbjmobile@gmail.com]

Hello Steve and thank you so much for your work. I've done as you say but I cannot get it to work. I copied your code, noticed that in your example it is as a Module not under the rptAccount Class Module that Microsoft placed in the project so I made a Module and pasted the code there. As I have done many times, I placed a textbox on the report and named it fNumSpecificDayInMonth. Under the report properties in the On Load line I clicked the ellipsis and checked that your code was there and removed the previous from the rptAccount class module. So it's all in place but the text box doesn't fill, it remains empty, there is no other error and the rest of the report works properly. I've set it's format property to general number and if I make a sub up that just says Report.rptAccount.fNumSpecificDayInMonth = 67, the number 67 appears in the box on opening the report. I did notice that in the data entry form that you left to load on database open, the Thursdays in Month box says 4 and not 5 (5 Thursdays in March). I don't know what I'm doing wrong and I apologise for the time I'm wasting for no outcome. I see that Micron is confused by the thread also, maybe he should have left an 'o' out !

[tbjmobile@gmail.com]

Hi Micron, thanks for the reply and please see Ssanfu's help.
Trying to answer your questions, the exercise is to email a monthly statement to customers and the integer required to fill a box in the rptAccount loads is to show the number of Thursdays that occur in the month that the account is prepared. I have explained elsewhere why Thursdays. The reason is so that clients can check the weekly charges and so that my daughter, for whom the database will be, doesn't under or overcharge people, bearing in mind she will only do it once per month.
The report is based on a table and while this might not be ideal, it works perfectly apart from this one item, and I haven't got around to the emailing part yet. It takes work carried out during the month and holidaymaker's arrivals and service during the month, both as subreports.
The month is shown on the top line of the report report as month() and after it I want to tell the client how many weeks the account covers, based on how many Thursdays are in the current month. The plan was to use vba to calculate how many Thursdays there are, and place it into the ThursinMnth textbox.
I hope this answers your questions adequately but please look at ssanfu's replies so that both of you don't waste time. I'm very aware of the time that has been invested in this newbies (seemingly) simple problem.
Thanks.

[Micron]

and the integer required to fill a box in the rptAccount
All I was asking for is "where"? I couldn't see the target in report design (it's a bit cluttered, which is not uncommon, but you allow names like text26 which isn't helpful). So I clicked on each one until I found what I think is the spot you want the number to go. Will get back to you.

[tbjmobile@gmail.com]

Hi Micron,
Yes, I never got around to renaming the textboxes, sorry. They are 3 in total called ThursInMnth, the first on is on the top line of the report between COVERING and WEEKS (to read COVERING 5 WEEKS) for March. The second is on the line beneath Month(Date()) Account, just before weeks management charge. The third is between 'Pool maintenance' and 'weeks at'.
Trevor.

[Micron]

OK here's where I'm at. I sent a PM to ssanfu apologizing for jumping in because I was under the mistaken impression that he couldn't open your db either. I discovered that was incorrect, and the db he posted was a modified version of yours. But no response from him yet.

So I've taken a look at his db and I see that when the Account Data Entry form opens, the form field is set to the number of Thursdays for the current month. That puts the form into edit mode (the field in your table isn't "saved"). Since there's only one record in that table, I don't know what you want to do. If there will be many records, then is there a point in having that info in that table? I'd say no. It should go into it's own table if you're going to store it. But why do that?

Try this and see if it solves your issue: based on ssanfu's db, in the report control for the Thursdays calculation, in the property sheet, for that control's recordsource, put
=fNumSpecificDayInMonth(Date())

That will simply run the same function that the form runs, but the control will not be bound, which means it's not getting the value from anywhere that you might be storing it (which as I've said, I see no point in doing based on your posts).
Then it looks like (4 is shown between covering and weeks)

[tbjmobile@gmail.com]

O.k. Micron,
I'll try that thanks. I foresaw the purpose of the Account Data Entry form as being that, as the month wears on between the last report 'send' and the next, there are some things that happen, that don't fall into the category of Bookings or Work (which are of course in the subreports on the rptAccount). These have to go in the 5 'Extras' lines and it would be better I thought, than entering these items directly into the table fields. That is the main purpose of the data entry form. Now during the multiple attempts to get this working I have from time to time reverted to adding a field for 'ThursInMnth' so that I could continue and could check other things. The '4' in the data entry form could have come from that. I had forgotten about it though. I'll try what you suggest and get back.
Thanks again.

[tbjmobile@gmail.com]

Hi Micron,
This is weird. I did exactly as you said, changed the control source in the ThursInMonth properties to =fNumSpecificDayInMonth(Date()) on the three instances on the report. Ran the report and it worked - 5 for March. Whoopee I thought, success at last. I changed my system date to April and re-opened and it said April 4 weeks hooray.
When I changed the system date back to today, it still said 4, I tried it a few times and it seems to pick up the Month (long) and the correct weeks from 4 to 5 once only and then can't get back to 5 for (say) August - not Hooray or whoopee. There are 2 observations from me, firstly the calculation of the management and Pool maintenance lines didn't work because they use ThursInMnth for that calculation. I'm not sure if putting the vba as part of a calculated control would work so I figured that I could use another control to make ThursInMnth = to something else and put that in these 2 lines. I haven't done that yet. I do of course get the parameter request for ThursInMnth on opening the report. Secondly, will the fact that the fSQLDate function is now not being called along with =fNumSpecificDayInMonth(Date()) affect things? Should I export the frmAccountDataEntry from the database?
Trevor.

[Micron]

There are things you're revealing as you go, or I/we are missing from your descriptions of how things work. I don't recall where you said you needed the value for other calculations, so just having the function call from the report control won't do. The change should have proved it would give you what you seemed to want in the report (it works for me). That you say it doesn't leads me to think that you're not refreshing the form or report - you're just altering your system date and looking at the still open report, expecting it to display the correct result. It won't, because simply making the report or form window active again doesn't cause another function call. And if you're looking at a still open table, you have to refresh that too (ribbon). Right now, the only way you could cause the function call again is to close/open the object after each system date change.

You can commit the value into the table by changing to this
Private Sub Form_Load()
Me.ThursInMnth = fNumSpecificDayInMonth(Date)
Me.Dirty = False
End Sub

and change the report control source back to ThursInMnth
Based on my probably flawed understanding of how you use this db, that should solve your issue.
EDIT:
2:30 AM and I can't get to sleep! Dang!
Maybe after I take care of what's going through my head. The call from your form to the function should occur every time you move to a new blank record, yes?

[tbjmobile@gmail.com]

Hi Again,
I hope you did get to sleep, I'd hate to think you were losing sleep on my behalf. I did think that I'd pointed you to the 3 instances of ThursInMnth on the report, and I definitely saved, closed and re-opened the report on every date change, not sure I tried Refresh every time but if closing and re-opening that shouldn't matter should it?
I'll try what you say when I have time later today and post again. Before I make any changes to what I have now though, I'll remove the other 2 instances to see what happens.
Thanks and regards,
Trevor.

[tbjmobile@gmail.com]

Hi again,Micron, and success ! What I decided to do in the end was to rename the remaining 2 controls in the calculations and make their control source =fNumSpecificDayInMonth(Date()) as well. Everything now works consistently, regardless of how many times I change the system date, even the data entry form. I can't thank you and the others enough for your help on this, it's been bugging me for about 10 days, now I can bumble on. Just off to leave feedback for you all now. Thanks again,
Trevor.

[ssanfu]

Sorry for being gone.... I got slammed at work and a 10 inch snow dump - lots of plowing........


Glad Micron stepped in and helped and get the problem is resolved...


The one thing that still bothers me is the linking fields (PK/FK). It is not recommended to have text PK/FK fields.
Having a text field "Cuscode" as the linking field is bad because if you someday want to change the customer code naming scheme, you will have to change the linking fields also. Difficult at best.
If you used the "ID" autonumber fields (PK), changing a customers's customer code has no effect.



Anyway, Good luck with your project and happy to help.....