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
60  4357 
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
Yes, the field is an expression in the query as ThursdayCount:CountThursdays(Date()) 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 '=CountThursdays(Date())'.
Thank you all again,
Trevor.
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
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.
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. TxtThursdayCount is a good alternative.)
Phil
O.k., understand. I'll get back when I've done it, might be an hour or so. Thanks for the screenshot.
T.
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.
Well it's been a steep learning curve for you, but I,m glad we were able to help
Phil
Yeah, I'm really pleased and a bit more au fait with Access. Thanks to you all.
Trevor.
NeoPa 32,556
Expert Mod 16PB
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.
Thank you NeoPa,
I thought that post #32 was the best, until that, I was dreading making the whole report again.
Regards,
Trevor.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Schlauberger |
last post by:
I am currently developing a VB.Net App that utilized an MS Access file to
store the data. My Report data source is a Parameter Query in MS Access when
the report loads the parameter is prompted...
|
by: Luminal |
last post by:
Greetings
I'm having some problems on my C# application. I'm using an access
database and I'm not able to do select queries with the ' character.
My code is this:
// some previous code like...
|
by: amith.srinivas |
last post by:
Hi all,
From a word macro in VBA, I am trying to create a report in
access DB. The report is based on a query with a parameter. I am using
Set rpt = Application.CreateReport
rpt.RecordSource =...
|
by: Brad |
last post by:
Thanks for taking the time to read my question.
I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own...
|
by: djackman |
last post by:
I have a small Access app I've slowly developed to generate reports from a MS SQL database.
One of these reports populates a temp table (tbl_VisitTemp) from a query that is then used for a...
|
by: CD Tom |
last post by:
I'm thinking about updating from Access 2007 to Access 2016 but have run into a problem. I can't find any place to compile my accdb to an accde. I've installed the office 2016 and can bring up my...
|
by: Silver993 |
last post by:
I have created Access 2016 Database with custom Icon. I can’t figure out what I am doing wrong. Here is the problem: When I tested the database in my developing Computer, it opens with my custom Icon...
|
by: Murphy |
last post by:
I am gettig ready to upgrade from Windows XP / Office 2007 to Windows 10 / Office Professional 2016. Are Access 2007 databases compatible with or convertible to Access 2016?
|
by: Eghnov71 |
last post by:
i am trying to track time spent in certain areas associated with our Communications site.
I have a table (just one table to keep it simple). Called Main Table. In it i have multiple columns for...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |
