423,849 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,849 IT Pros & Developers. It's quick & easy.

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

P: 62
I don't know if the forum covers queries or just vba but here goes.
I'm looking for a way to get the result of a query onto my report. The report is sent to customers monthly and is based on a table called tblAccount. I need to calculate weekly charges on this account and to do that, I need to have the value of the number of Thursdays in the current month, 4 or 5. The field for this is currently in the table and is called Thurs1.
I have made a query, the result of which gives me the relevant number 4 or 5 depending obviously on the current month. I need to get that value into a control on the report. The number in this control will later be used to calculate weekly charges x the number (weeks in the month, based on the Thursdays).
There are lots of other calculations on the report and some vba code to send these accounts by email. All of the calculations are based on sub-reports, or on the fields in the table, so I could calculate the value anywhere, so long as I can pass that value to a textbox on the report.
Thank you for any suggestions that you may have.
May 2 '18 #1
Share this Question
Share on Google+
60 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,704
You could have an unbound text box that uses DLookup as it's Record Source to get that value whenever the Report is opened. I don't know exactly how that number of Thursdays is saved, but it could work.

Hope that hepps.
May 2 '18 #2

NeoPa
Expert Mod 15k+
P: 31,121
I think I'd be very cautious of suggesting such a solution Twinny.

Like any other field on a Report or Form, they are best populated by the underlying .RecordSource. If the OP has that in their query then it should simply be bound to a Control (TextBox probably).

Data isn't stored on Forms or Reports. They're simply used to reflect data held elsewhere - usually by linking to a query or directly to a table even. The value could be returned in a Function, but even that would be generally called while populating the underlying query.

There is a time and place for using DLookup(), but it's definitely not a common time or place. IE. It's rare that a good design will include references to DLookup() - especially not within a query.
May 3 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,704
Sage advice, as usual, NeoPa!

So, keeping this in mind, in this case, just add the table with the Number of Thursdays to the Query? I don't know the structure of that table, so greater details would need to be provided to give specific details.
May 3 '18 #4

P: 62
Hi Guys and thanks for the response.
Details;
The rptAccount's recordsource is the tblAccount and you, Twinnyfo, helped me enormously to fix up the vba to send the reports out by email in vba, BUT there were 58 posts in the end so you may be wary about starting again with me !
The account table is weekly charges for cleaning and materials used. There is a balance brought forward and as I said, two subreports, one for work and one for occupation. All of these items are summed, tax added and a grand total arrived at and then the account is sent out. I have done all the calculations and it all works correctly and now, thanks to you, I can email them all at the end of the month.
At the moment though I have an update query that populates a field in the table called Thurs1, a number field. This forms the basis for calculating all the weekly charges in the report so it all works, but when there is a 5 week (5 Thursdays) month following a four week one, it relies on my daughter remembering to update the column with the 4 or 5 update query. If she forgets, she loses one week's money from 30 odd cuastomers - not good.
So I devised a query, the result of which provides the number 4 or 5. and I want to find some 'automated' way of getting this result into a control, textbox, whatever, onto the report when she opens it to send it, (entering the items along the month is done in a data entry form that populates the table's fields).
So I need a way of populating the field in the table (Thurs1) or the control on the report (whatever name) with the 4 or 5 from the query.
I hope this is enough info..
May 3 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,704
Trevor,

It sounds like your Table has one field and one record? If this is so, go to the Query used for the Main report (or for the report that has the text box for the number of Thursdays). In the Query editor, simply add the THursday Table, and include the field that holds the value for the number of Thursdays.

Now, in the report that uses the number of Thursdays, just bind the text box to that new field as its record source.

I "think" this is the solution you are looking for. You will still be performing some report-level calculations. However, to avoid those calculations, perform all the calculations at Query-level, and include those as additional fields.

Hope that makes sense--and I hope it hepps!
May 3 '18 #6

P: 62
Hi Twinnyfo,
Unfortunately no,the table has about 7 fields and 30 ish records and then there are the sub-reports that can have several records each. I reallt just need an idea of how to fire the query when the report is opened.
I suppose I could try to write some vba to find the number of Thursdays and put the procedure on the report as 'OnOpen' or 'OnLoad' but that will probably take me a long time at my skill level!
Trevor.
May 3 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 2,704
Trevor,

Can you please go into some rather excruciating detail about where, exactly, the value for the number of Thursdays is stored? how do you calculate it and how do you store it? Right now, that is the long pole in the tent, as to how you come up with it in the first place. Because that some procedure could be added to any Query....
May 3 '18 #8

P: 62
The number (4 or 5) is calculated as the result of a query as I said, so it isn't stored. Neither would it be stored on the report, it would just be there whilst the report was opened and checked/sent. If it could be stored it would be in the table as field Thurs1. That way it could be placed on the report because the report's record source is the table so it's recordsource would be Thurs1, as most of the other controls. That is to say like the weekly cost of pool cleaning for instance, this is a weekly charge, one of three, that uses the Thurs1 to calculate whether the charge is to be levied 4 or 5 times during that particular month of the account. The problem is that I don't know how or if I can get the result of a query into either the field Thurs1 in the table, or the relative control on the report as it opens each time. This way, no-one can forget to run an update query, or enter the number in the table fields manually. You say that the same procedure could be added to any query, which I understand, but how would I then place the value each month into Thurs1 in the table, or a control in the report?
Trevor.
May 3 '18 #9

twinnyfo
Expert Mod 2.5K+
P: 2,704
How do you calculate the 4 or 5?????

If you currently calculate a 4 or a 5 for ANY month, then any time you send out an invoice, for ANY month, just caclulate that value. You shouldn't have to "save it" anywhere, because the value changes based upon the month for which the invoice is going out.

For example, for 2018, the months of January, February and April only have 4 Thursdays, but there are 5 in March and May. This would be calculated (using the same method you use to calculate in your udate query) and that would be one of the fields in the report's query.

Make sesne?
May 3 '18 #10

P: 62
Hello again,
Yes, that is the whole point of the question, given that I CAN calculate these vales, HOW do I make them appear on each month's report automatically? I already have an update query that updates the value of tblAccount.Thurs1 to 4, I also have another update query that updates tblAccount.Thurs1 to 5, AND I have a query that calculates the value for any given month without any user input. My question is how to get that value into the report so that I can use it to calculate weekly charges within that report. How do I do that automatically, otherwise I can use the update queries but someone may forget to do so because this excercise is only once every month.
To calculate the 4 or 5 I used;
a) Find the first date of the current month using DateSerial and Month() and Year().
b) Find the last date in the month in a similar manner.
c) Find how many days in the month (b - c) + 1.
d) Find the day number on the first day of the month (Weekday of a ).
e) Find out how many days to the first Thursday, I used a multiple IIf for this.
f) Deduct this from the days in the month (c - e).
g) If the answer to g is less than 29 the month has 4 Thursdays (or any day you choose in e ) if not, the month has 5 Thursdays.
Regards,
Trevor.
May 3 '18 #11

twinnyfo
Expert Mod 2.5K+
P: 2,704
Your method for finding the number of Thrusdays seems a bit overly complexified.

Create a new public module (stand alone) and put this in:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function CountThursdays(InvoiceDate As Date) As Integer
  5. On Error GoTo EH
  6.     Dim intYear     As Integer
  7.     Dim intMonth    As Integer
  8.     Dim datDays     As Date
  9.     Dim intCount    As Integer
  10.  
  11.     intCount = 0
  12.     intYear = Year(InvoiceDate)
  13.     intMonth = Month(InvoiceDate)
  14.     For datDays = DateSerial(intYear, intMonth, 1) _
  15.         To DateSerial(intYear, intMonth + 1, 1) - 1
  16.         If Weekday(datDays, vbSunday) = 5 Then
  17.             intCount = intCount + 1
  18.         End If
  19.     Next datDays
  20.  
  21.     CountThursdays = intCount
  22.  
  23.     Exit Function
  24. EH:
  25.     MsgBox "There was an error counting Thursdays!  " & vbCrLf & vbCrLf & _
  26.         "Error: " & Err.Number & vbCrLf & _
  27.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  28.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  29.     Exit Function
  30. End Function
Now, anytime you want to count the number of Thursdays in a month, just send any day from that month to this Function and it will return the number of Thursdays.

Then, in your query, just use that as one of your fields, based upon the invoice date of your customers.

Hope this hepps!
May 3 '18 #12

P: 62
Thanks for that. I'll see what I can do tomorrow and get back.
Trevor.
May 3 '18 #13

NeoPa
Expert Mod 15k+
P: 31,121
TrevorJ:
I really just need an idea of how to fire the query when the report is opened.
I'm afraid such a statement indicates you haven't been paying close enough attention to what's been posted Trevor.

It doesn't work well to run the Report and, somehow, trigger an extra query to be run. That's wonky thinking.

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.

It's a different way of thinking than may be natural to most people, but the fact is, it works. You can produce what you need more easily once you take that on board.
May 3 '18 #14

NeoPa
Expert Mod 15k+
P: 31,121
This version of the function is more succinct, if a little less obvious to read :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function CountThursdays(ByVal datInvoice As Date) As Integer
  5.     Dim intDays As Integer
  6.  
  7.     datInvoice = DateValue(DateAdd("d", 1 - Day(datInvoice), datInvoice))
  8.     intDays = DateDiff("d", datInvoice, DateAdd("m", 1, datInvoice)) _
  9.             + Weekday(datInvoice, vbFriday) - 1
  10.     CountThursdays = intDays \ 7
  11. End Function
May 3 '18 #15

P: 62
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.
May 4 '18 #16

NeoPa
Expert Mod 15k+
P: 31,121
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.
May 5 '18 #17

NeoPa
Expert Mod 15k+
P: 31,121
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.
May 5 '18 #18

PhilOfWalton
Expert 100+
P: 1,376
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
May 5 '18 #19

P: 62
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.
May 6 '18 #20

PhilOfWalton
Expert 100+
P: 1,376
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
Expand|Select|Wrap|Line Numbers
  1. ThursdayCount:CountThursdays(InvoiceDate)
  2.  
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
May 6 '18 #21

P: 62
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.
May 6 '18 #22

P: 62
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:CountThursdays(invoicedate)
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.
May 7 '18 #23

PhilOfWalton
Expert 100+
P: 1,376
When you run the query, does the ThursdayCount show the correct value?

Phil
May 7 '18 #24

P: 62
No, It gives an error - Un-defined Function 'CountThursdays' in Expression
May 7 '18 #25

PhilOfWalton
Expert 100+
P: 1,376
Re-reading your last post, change ThursdayCount:CountThursdays(invoicedate) to

Expand|Select|Wrap|Line Numbers
  1. ThursdayCount:CountThursdays(Date())
  2.  
Also make sure the "Show" row is ticked.

Phil
May 7 '18 #26

P: 62
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.
May 7 '18 #27

P: 62
I don't have anything referring to Thursdays in the table any more as you said I shouldn't have.
May 7 '18 #28

PhilOfWalton
Expert 100+
P: 1,376
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

Expand|Select|Wrap|Line Numbers
  1. ?CountThursdays(Date())
  2.  
What is the result?

Phil
May 7 '18 #29

P: 62
Yes, I did that, see post #45. 'Compile error 'Expected variable or procedure, not module''
May 7 '18 #30

PhilOfWalton
Expert 100+
P: 1,376
I have feeling we are cross posting.

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

Phil
May 7 '18 #31

P: 62
Yep, copied and pasted
Public Function CountThursdays(InvoiceDate As Date) As Integer
May 7 '18 #32

PhilOfWalton
Expert 100+
P: 1,376
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
May 7 '18 #33

P: 62
Hi Phil, I did, #32, 37 minutes ago.
May 7 '18 #34

P: 62
Just to clarify though, the name of the actual module IS CountThursdays
May 7 '18 #35

P: 62
Hi Phil, Are you still with me?
TrevorJ
May 7 '18 #36

twinnyfo
Expert Mod 2.5K+
P: 2,704
Trevor,

Rename the Module modCountThursdays.

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())
May 7 '18 #37

P: 62
Yes, that returns a 5. Shall I change the system date and try a few different months? Or what next?
May 7 '18 #38

twinnyfo
Expert Mod 2.5K+
P: 2,704
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.
May 7 '18 #39

P: 62
Just to clarify again, what do I place on the report and as it's contro; source, a textbox called modCountThursdays. But I have to pass the Date() to the function, exactly how please?
May 7 '18 #40

twinnyfo
Expert Mod 2.5K+
P: 2,704
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.
May 7 '18 #41

P: 62
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.
May 7 '18 #42

twinnyfo
Expert Mod 2.5K+
P: 2,704
Have you changed the report’s record source to the query you just created?
May 7 '18 #43

P: 62
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(Date()). 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(Date()).
May 7 '18 #44

PhilOfWalton
Expert 100+
P: 1,376
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
May 7 '18 #45

P: 62
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.
May 7 '18 #46

twinnyfo
Expert Mod 2.5K+
P: 2,704
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.......
May 8 '18 #47

NeoPa
Expert Mod 15k+
P: 31,121
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.
May 8 '18 #48

P: 62
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:CountThursdays(Date()) [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?
May 8 '18 #49

NeoPa
Expert Mod 15k+
P: 31,121
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.
May 8 '18 #50

60 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.