Need to get result of query onto a report, Access 2016

[TrevorJ]

Hi Guys, Can we leave this until tomorrow afternoon now please, can't see the text for yawning? If you have the next suggestion, please post it if you wish and I'll get back.
Thanks,
Trevor.

[twinnyfo]

Trevor,

For tomorrow..... if you have the number of Thursdays in the query, no need to calculate it o the report, just use the value in a text box.

@Phil, I think we can continue to tag team this one.... PM me if you have questions...... .

[NeoPa]

TrevorJ:
Hi Phil, I did, #32, 37 minutes ago.

Just to clarify though, the name of the actual module IS CountThursdays

Hi Trevor.

I can see you're getting a little confused. Post #32 includes the name of the procedure; not the module. We must remember that it's easy to get confused in an area that is nevertheless very familiar to the experts. We do try to remain patient - it's just hard sometimes when we see stuff that doesn't make sense to us. So we'll bear with you if you can bear with us.

It may help to understand that what you posted, and what Twinny & I posted for you, are examples of code that are modules - but they don't include the name of the module. They include procedures, whose names you can see. The name of the module itself is one of the module's properties though. You can see it in the list of modules and in the properties list of the selected module.

I suspect once you renamed the module itself you found that the project compiled and was usable.

I do support work a fair bit and I can say that even following simple instructions can be hard and unobvious when you don't have the understanding or experience to understand what you're doing. Simple instructions are only simple for those that know what's going on. It seems to me that you're doing as well as can be expected when attempting to follow what's been suggested. Do remember though, it's important always to respond to each post and answer all questions, as Phil has already commented on.

Doing independent research is also highly appreciated. It looks like you're getting there and making good progress for a fairly steep learning curve. Keep up the good work.

[TrevorJ]

Hi Folks, Thanks for your posts. As NeoPa said, I hadn't realised until at least half way through this thread that some kind of parameter had to passed to the function. Once I had read about the properties of the necessary parameter/s within the parentheses, all was clearer, some might say obvious. Even in the Access 2016 bible though did I read that the function had to have a name specifying that it was in a module, I do now.
So brimming with all this freshly acquired knowledge and a little more experience, it now works. It might still be useful to others watching this thread though, to know that the field in the query doesn't need the mod name, neither is it needed in the text box. The field expression is as Phil instructed - ThursdayCount:C ountThursdays(D ate()) [or whatever parameter the function calls for between the parentheses in it's name].
The text box's name is ThursdayCount and it's control source is CountThursdays( Date()) 'calling' the function from the report.
I hope that I've got this right and I hope you will post a correction if not but I'm just trying to quantify it all in one place.
So thank you all three very much, for your expertise and perhaps more - your patience.
I do have one further question though. I need to put the result of the function ('5' for May) into three places on the report saying for instance '5' weeks charges for this and '5' weeks charges for that. I have read that you shouldn't use the same text box name on a form or report more than once - so - do I use three names and call the function 3 times for the (obviously) same value three times or do I try to refer to the ThursdayCount value each time AND in the resulting calculations on the report?

[NeoPa]

Hi Trevor.

It's not absolutely clear to me whether you have the field set in the query used for your .RecordSource, or you have a control set in your Report.

If the former (as advised - but maybe before you were ready to understand that correctly) then you simply reference that same field in any control on your Report. If the latter then change it to the former is the obvious solution. Otherwise, yes, you'd need to call the function multiple times for each record shown. Not a tidy approach.

[PhilOfWalton]

Oh dear Trevor! You're still not getting it.

You say you have a query called 'qryAccnt' which is now the RecordSource for your report.

With NOTHING ELSE OPEN, run that query.
Is there a field in it called "ThursdayCount" , currently showing a value of 5?

Phil

[TrevorJ]

Yes, the field is an expression in the query as ThursdayCount:C ountThursdays(D ate()) so I have dragged that query field into the report and it works. I can therefore put 2 other textboxes onto the report and the source in theirs control will be the '=CountThursday s(Date())'.
Thank you all again,
Trevor.

[PhilOfWalton]

Trevor, I can't help any further as you won't answer the questions.

I asked you to RUN the query and tell me whether there is a field called ThursdayCount with a value of 5.

Phil

[TrevorJ]

Hello Phil, I do apologise, I was flushed with success and away to finish the report. The answer is yes and I did mention that all was working on page 1. I also tried the query in other months by changing the system date and it all seems to be working. I then asked NeoPa another question which he has answered and I replied. I'm sorry again and thank you so much for your help, all of you.

[PhilOfWalton]

Good.
You now need to add a Textbox on your report with the ControlSource being ThursdayCount. You will find it by adding the textbox and then pressing the down arrow on the Data Tab of the properties sheet and scrolling down to ThursdayCount

Excuse the irrelevant stuff, but you should see something like this:


Just click on the ThursdayCount - Don't do anything else.
I have named the Textbox ThursdayCount (Bad habit from the past, but you can call it anything you like. TxtThursdayCoun t is a good alternative.)

Phil

[TrevorJ]

O.k., understand. I'll get back when I've done it, might be an hour or so. Thanks for the screenshot.
T.

[TrevorJ]

Hi Phil, Yes. I've done it all now, including calculations and it's all working properly. I must say that your comment about the changing of the table as the recordsource to a query wouldn't be too difficult, I was a bit sceptical, but you were smack on. It was so simple really.
So again, thank you all so much, I would have spent months finding out what I've found out here.
Trevor.

[PhilOfWalton]

Well it's been a steep learning curve for you, but I,m glad we were able to help

Phil

[TrevorJ]

Yeah, I'm really pleased and a bit more au fait with Access. Thanks to you all.
Trevor.

[NeoPa]

Hi Trevor.

Phil has taken you through, in detail, what I was trying to lead you towards. Excellent. Don't let confusion and temporary failure get you down. You're making good progress.

As you rightly say, you're learning a great deal. It's still hard for us to be patient and allow for your mistakes, but we all know that they're natural results of trying to get up to speed in a complicated area. I'm very pleased for you. It's an exciting time and your new understanding will be very helpful for you going forward.