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

[TrevorJ]

Sorry guys but you are assuming that I know more than I do.
NeoPa says 'What you need, and what Twinny is trying to guide you towards, is to include the data in the .RecordSource of the report itself. That way it's just there. Available when you need it.'

I have said that the recordsource for my report is tblAccount and that it has a field called Thurs1, so am I trying now to update the data in that table ? If so, that would be fine, so long as I can make it update the field before the report populates.

Twinny, you kindly sent me some vba cade and said 'just send any day from that month to this Function and it will return the number of Thursdays.'
Where do I just send the date from, what to and where will it return the answer?

Trevor.

[NeoPa]

TrevorJ:
I have said that the recordsource for my report is tblAccount and that it has a field called Thurs1, so am I trying now to update the data in that table ? If so, that would be fine, so long as I can make it update the field before the report populates.

I've no idea what connects what I said to what you've just said/asked. I can only re-state what I was talking about and hope you get it.

I was pointing out that, generally speaking, getting data separately to add to what is available in a Report object is not a good idea. Reports are designed to have a .RecordSource property that is there to provide the information required. So, you're thinking should be :
How do I make sure that the information I need is contained within the data provided by the .RecordSource?

I took your statement "I really just need an idea of how to fire the query when the report is opened." (quoted directly in my post #14) to indicate you were thinking about running a query separately from within the Report, or even somewhere else at the time the report is run. If that's a correct understanding of your meaning then I have to point out that it is a wrong-thinking approach that will certainly lead you into all sorts of trouble.

If this is still all too much to grapple with then don't worry too much. TwinnyFo is taking the lead on this one and he certainly understands my comments. If you follow his guidance you'll never go very far wrong.

[NeoPa]

Again, if my post #15 doesn't mean much to you then it certainly will to TwinnyFo. It was as much for their benefit as yours when I posted it anyway.

Good luck with your project.

[PhilOfWalton]

Sorry, a late butt in.
There should not be a field Thurs1 in your rTblAccounts. This is a calculated value given by the 2 excellent functions provided by Twinnyfo & Neopa.

The report should be based on a query (based on your TblAccounts and a Field called ThursdayCount given by one of the 2 aforementioned functions.

Phil

[TrevorJ]

I've found some reading since my last post so I know a (very) little more than before. My problem was that no-one told me about passing values to the function. I should of course have known that but on the other hand, that's probably why I'm asking the questions!

NeoPa,
You say that my thinking should be 'How do I make sure that the information I need is contained within the data provided by the .RecordSource?' Does that mean that it should be in the tblAccount? That is where the records are.

Thanks also for your input Phil. Are you saying though, that I have to base my whole report on a query? It's quite a complicated report and I can't face re-making it right now. I have removed the Thurs1 field from the tblaccount, but if I understand correctly the 'field' that you refer to as 'ThursdayCount' should be a control on the report as 'CountThursdays ', i.e. the name of the function and be no part of the table? If that is the case, and I want the Thursdays integer to be accessible on the report without user intervention, how do I call the function? If I place a control on the report called 'CountThursdays ' and make an event procedure - =CountThursdays (Date()) that won't work. Maybe I should say again that I want the calcultion of how many Thursdays in the account month to be ON the report as (or before) it opens. If that can't be achieved I will have to rely on the user rembering to enter in the amount manually, which is what I want to avoid.

I'm obviously missing some very basic concept because at the moment these two comments seem to be contradictory to each other.

[PhilOfWalton]

Trevor, with a bit of luck you won't have much re-designing to do on your report. I would add that I guess that 90% or more of forms & reports are based on Queries, NOT tables.

So for simplicity, create a query called QAccount based on all the fields in TblAccount + one called ThursdayCount.

Whilst NeoPa's VBA is more succinct than Twinnifo's, the latter is much more readable, so as you say you are not that experienced, I suggest you use the Twinnyfo version.

So the ThursdayCount should look like this in your query

ThursdayCount:CountThursdays(InvoiceDate)

I am assuming there is an InvoiceDate or something similar.

Save the query and change the RecordSource of your report from TblAccount to QAccount

Phil

[TrevorJ]

Yes, I see what you mean Phil. There are two sub-reports on the report though, and quite a few calculations including deducting money received, calculating and adding tax etc.. I'm not yet sure how to add in the totals of the sub-reports in the query but I'll look at it tomorrow because I've spent most of the day reading up on the vba part. I'll get back.

[TrevorJ]

O.k. I've done as you suggested, to keep in line with my naming convention, I've called the query 'qryAccnt'. I have ALL of the fields except ID_Acc in it, plus an expression - ThursdayCount:C ountThursdays(i nvoicedate)
I will just pass the date() as the parameter but I feel I'm back where I started, asking '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.
Trevor.

[PhilOfWalton]

When you run the query, does the ThursdayCount show the correct value?

Phil

[TrevorJ]

No, It gives an error - Un-defined Function 'CountThursdays ' in Expression

[PhilOfWalton]

Re-reading your last post, change ThursdayCount:C ountThursdays(i nvoicedate) to

ThursdayCount:CountThursdays(Date())

Also make sure the "Show" row is ticked.

Phil

[TrevorJ]

Still the same error. Even after re-starting Access. I have the code in a module, and if I enter ? CountThursdays date() I get a compile error of type mismatch. I've tried all permutations of a proper date, with and without parentheses a comma etc, but still the same.

[TrevorJ]

I don't have anything referring to Thursdays in the table any more as you said I shouldn't have.

[PhilOfWalton]

Excellent, we're getting somewhere.

For the moment, forget the query & report and concentrate on the VBA function CountThursdays. Does it compile OK?
If so, in the immediate window type

?CountThursdays(Date())

What is the result?

Phil

[TrevorJ]

Yes, I did that, see post #45. 'Compile error 'Expected variable or procedure, not module''