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

[PhilOfWalton]

I have feeling we are cross posting.

What is the name of the Module that contains Twinnyfo's function "CountThursdays "?

Phil

[TrevorJ]

Yep, copied and pasted
Public Function CountThursdays( InvoiceDate As Date) As Integer

[PhilOfWalton]

Trevor, I'll try to put this gently to you. If you want help and one of the "Experts" asks a question, please answer it.

If thing are going wrong with you Db, it is sometimes to examine every possibility and this may require many small steps to solve the problem

I repeat the question - What is the name of the Module that contains Twinnyfo's function "CountThursdays "?

Phil

[TrevorJ]

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

[TrevorJ]

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

[TrevorJ]

Hi Phil, Are you still with me?
TrevorJ

[twinnyfo]

Trevor,

Rename the Module modCountThursda ys.

Try compiling. If any errors, post the error number and/or, the line that produces the error.

If it compiles OK, then try this in the immediate window:

?CountThursdays (Date())

[TrevorJ]

Yes, that returns a 5. Shall I change the system date and try a few different months? Or what next?

[twinnyfo]

So, now we know that the function I gave you works. Now just use it in the query that serves as the Record Source for your report.

[TrevorJ]

Just to clarify again, what do I place on the report and as it's contro; source, a textbox called modCountThursda ys. But I have to pass the Date() to the function, exactly how please?

[twinnyfo]

Trevor,

Please try to pay attention to the information we have been giving you. Instead of using the table as the record source for your report, build a query. In that query, you use every field needed from that table for the report. Include, also, a field, as described earlier, in Post #26, which will give you the number of Thursdays for the invoice in question. If you base the calculations on the date of the invoice (as the month which has the Thursdays for calculating) then that is the date that you are sending to the Function. It is all part of the query. If you need to make additional calculations, make those in the Query as additional fields. These fields can then be placed on the report using text boxes.

This is just like using the table, except that it is a query. Again, as stated earlier, very few modifications need to be made to your report.

[TrevorJ]

Hello,
I am paying attention, but if I'm missing something or I don't understand something, I must ask again or I would be wasting everyone's time.
I have created a query with all of the fields but as I said in my post #23 six hours ago 'How do I get the result of this query expression onto the report?' The expression isn't on the list of fields even though it is in the query and I've changed the recordsource. Since then Phil and I and later yourself have been working on the module working, but the query field still doesn't show up in the drop down list of the query's fields. All the other fields do.

[twinnyfo]

Have you changed the report’s record source to the query you just created?

[TrevorJ]

Yes, I did that this afternoon. The report works more or less, I of course need to change a few things on the calculated controls because the calculations aren't yet in the query. Generally though I can see that it will work. All I need to know is how to put that 'call' to the function onto the report. I have placed a textbox for ThursdayCount into the report as ThursdayCount(D ate()). ThursdayCount is the name of the expression that Phil gave me (post #26) but when I run and save the report and re-open it I get a parameter messagebox asking for ThursdayCount and another for ThursdayCount(D ate()).

[PhilOfWalton]

Sorry, out to bridge.

@Twinnyfo.
I had guessed the problem was a conflict of Module Names & Function Names, but was progressing slowly for obvious reasons. Do you want to take over from here or would you rather I continued?

Phil