How to count data from another query by date

Brian62

Registered User.
Local time
Today, 08:58
Joined
Oct 20, 2008
Messages
159
I have a query that is used to pull data to the report by between dates to include writeups and yes, no, and N/A's per question (combobox). Works great.

I created another query to pull the totals of yes, No, and N/A's by column off of that query. Here is one example of one of the questions..

Code:
Doc28-51No: Sum((Abs([Doc #28-51]="No")))
Doc28-51Yes: Sum((Abs([Doc #28-51]="Yes")))
Doc28-51NA: Sum((Abs([Doc #28-51]="N/A")))

I created another query off of this query to total the yes, no and n/a to place at the report footer by sub-report from this query giving me the totals I need. It will pull all the column totals but not by the between dates I need.

Code:
Total No: Sum([Doc2No]+[Doc25No]+[Doc12-5No]+[Doc28-51No])


I need the query to pull by the between dates from the report query when I enter it in.

Is there a way that this can be done. I hope I explained it well enough...
 
Where are the dates coming from? Are they hard coded onto the query?
 
I enter the date of the audit in the form. In the query for the report I use this code:

Between [Enter Start Date Like: 01/01/09] And [Enter End Date Like: 12/31/09]

As the report opens I put in the date range I want. I need the count to reflect the totals from that range.
 
I don't understand. If the records are limited by that date range shouldn't it be counting the filtered records?

If you've got a subreport that performs the count then you also need to limit the subreport's record source. There's no way to retrieve parameter values from a query so the only way would be to create a pop-up form and in the criteria of the query you point to the textbox like this:

Between [Forms]![NameOfPopupForm]![StartDateTextboxName] And [Forms]![NameOfPopupForm]![EndDateTextboxName]
 
I understand now partially what you were saying. The pop up form is my sub-report that has the totals.

I am still confussed on how to fill the code in the query. This is what I have so far:

Between [frmRDStudyAudit]![NameOfPopupForm]![Audit Date] And [frmRDStudyAudit]![NameOfPopupForm]![Audit Date]

Here is the codes in the sub-report query neamed QryRDStudyAuditTest1.

Total Yes: Sum([Doc2]+[Doc25]+[Doc12-5]+[Doc28-51])

Total No: Sum([Doc2No]+[Doc25No]+[Doc12-5No]+[Doc28-51No])

The name of the query that is pulling this information is from QryRDStudyAuditTest.

I tried to enter the Between code in both columns but it doesn't give me anything.

I truly appreciate your help on this!!!
 
Last edited:
I got part of it to work but I have to enter the dates 3 times each for start and end date.

In the query that pulls for the report I entered this:

Field Start Date: [Audit Date]
Criteria [Enter Start Date Like: 01/01/10]

Field EndDate: [Audit Date]
Criteria [Enter End Date Like: 01/01/10]

There are the 2 other queries (listed above) after that, on the last query is where I get my correct numbers. I do not have the "Audit Date" in either of the two but when the report comes up I have to enter enter the start/end date multiple times. Is there a way I can only enter it once? I have a feeling the two other quieries are what is causing the problem but have no idea how to correct it.
 
The pop-up form will act as the form for entering the criteria. The query will feed off the the criteria from the pop-up form's textboxes.

So just before your report opens, open the form to ask for and collect the date criteria, hide it, then open your report. If you set the criteria of your query the way I mentioned you wouldn't need to do anything else to the query.

The caveat is, the form must remain open during the period the report is open, and it must open before the query is run.

Is that clearer?
 
I tried everything you asked but I may have done it wrong. This is very frustrating when I do get most of what your saying but what I don't makes me cringe. I attached part of my database so you can look at it and see what I am doing wrong if you don't mind looking at it.

Thanks for your help you have provided!!

The form name is: frmRDStudyAudit
The report name is: RptRDStudyAudit
 

Attachments

But you've not created the pop-up form? As I explained, all the pop up form will do is to collect the start and end dates so you simply need to create a form and place two text boxes there where the user will enter the start and end dates. Using the syntax I gave you (i.e. [Forms]![NameOfPopupForm]![NameOfStartDateControl] for example), substitute it for the [Enter start date Like 01/01/01/] criteria.

So effectively instead of the criteria popping up twice asking for start and end date, the form will open and you enter the dates in those textboxes. The query will feed off that date entered from the form directly. Keep the form open.
 
I created the popup form and named it frmpopup. I placed two text boxes there but my question is should they be unbound? If they are to be named, what name should they be called? Right now there Text4 and Text5. Also what should the record source be?

The query I am using for the totals is qryrdstudyaudittest1.

Column1:
The field name is: Total Yes: Sum([Doc2]+[Doc25]+[Doc12-5]+[Doc28-51])
The criteria is: Between [frmrdstudyaudit]![Frmpopup]![Text4] And [frmrdstudyaudit]![Frmpopup]![Text5]

Column2:
The field name is: Total No: Sum([Doc2]+[Doc25]+[Doc12-5]+[Doc28-51])
The criteria is: Between [frmrdstudyaudit]![Frmpopup]![Text4] And [frmrdstudyaudit]![Frmpopup]![Text5]

frmrdstudyaudit is the form where I enter the information.
frmpopup is what I show above.

This is what I have tried but still won't work.

You are propably getting as fraustrated as I am and I really appreciate your help.
 
Your syntax is incorrect. The format is:

[Forms]![NameOfPopupForm]![NameOfTextbox]

The only things that need changing are in red.
 
What should the source record on the frmpopup be?
Do I keep the Text boxes unbound?
I entered the text as you suggested but comes up with a complex error.

I have atached the DB so you can see what I have done thus far.
 

Attachments

It would be much better you tell me what the error message says. It's too much hassle downloading and opening files.

On that note, what is the error message? Plus aren't you supposed to have two text boxes - one for start and the other for end?

The form will be unbound and the textboxes will be unbound. The form is simply there for taking input for your query, not for data manipulation.
 
Here is the error I am getting:
This expression is typed incorrectly, or it is to complex to evaluate. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I have the two boxes and they are unbound as text 4 and text6 as well as the popupform. I think it has to do with the query but not sure.

I think the problem is that I have 10 records based on personnel on the page header that I audited. Each of them would show there audit report broken down by name on the report. The total is based off of each of the same doc number by each personnel. That's maybe why the popupform shows the text box multiple times and strill give me the error.
 
Last edited:
Just reopened your attachment and I still see only one textbox, i.e. text4. May you forgot to include it in that particular db.

On looking at the form I don't see which button opens the report. Maybe you can explain the flow of things?
 
I have attached the updated DB with the command button to open the report. Also added the other text box.

Here is the flow of things as requested starting witht he form.

The top form header uses tblCommitteeInfoEntry.
Each of the subforms uses RDStudyAuditSectionA which are in 6 parts up to RDStudyAuditSectionH.

I have one Report (RptRDStudyAudit) that the form opens to.

The quieries I use to get the numbers I need to show on the report are:
gives me the totals for each doc# broken down to Yes and No. Later I will add the N/A with Yes. Just trying to get through this part first.

QryRDStudyAudittest1 gives me the toal of all yeses and No's which I need to show on the bottom of the report.

When I open the report from the form it does not give me anything but errors. Now with the new DB attached you will see what I am talking about. I only have in the DB is what relates to this portion of the database.

I hope this helps!! Thanks!!!!!!!!
 

Attachments

You created a pop up form without an OK button. When you had your query taking the input I'm sure there's normally an OK button to confirm the user has finished entering a date?

See attached how it's done.
 

Attachments

Thanks for the help but there is a problem when I try to enter two different dates like 10/12/10 and 10/13/10. The report will come up blank even though that there is data to support those dates.
 
All I did was set up your form for you. In the query criteria you need to have the BETWEEN predicate. So it should be this:
Code:
Between [Forms]![Frmpopup]![txtStartDate] And [Forms]![Frmpopup]![txtEndDate]
 
I really appreciate the help you have given me. Thanks for all you have taught me. Thanks!!!!!!
 

Users who are viewing this thread

Back
Top Bottom