Adding queries to a report

[tsp120]

I am trying to create a somewhat complex report. The 'Record Source' for the report has become long and detailed, and I no longer believe I can successfully add anymore statements to the query to get all of the info I need. I am wondering if there is a simple way in a report where I can add a textbox and add a simple sql statement into the control source or something like that. If this is possible, how do I add it to the control source and how do I use information from other textboxes already on the report. If it is not possible and you understand my problem, could you offer any tips or suggestions on how I can add more statements.

Thanks,
Tim

 

[randysmid]

Hi,
You can set up a form, and have various criteria presented. For instance, let's say you have customers all over the world, and want to select various ones based on a specific criteria. You may want to allow selection based on Country, Customer Type (e.g., retail vs wholesale), YTD sales, current balance (e.g., under $1,000), etc.
I wrote two different FAQ's on how to accomplish this. First, you can send the criteria directly to the query. See faq703-2696
The second technique is to use "filter". See faq703-2657

HTH,
Randy Smith
California Teachers Association

 

[tsp120]

I think I understand what you were telling me to do, and it does seem like it might work for me. Let me explain my situation a bit further. Right now in my record source for the report I have information regarding tests that were conducted, including the name of the product, it's id number, etc, etc. These tests were run in one of four different modes. So I want to count the occurances for each product and for each mode that were labeled as "Proper" (successful) or had an error type labeled (unsuccessful). I believe what you are telling me to do is to create forms, and for example in the first form, for each product, I count the number of occurances of "Proper" and then in the report I call the values obtained from that given textbox from the form.

First off, let me know if I understand your suggestion correctly.

Second, if I do, I am currently trying to call the forms value from the report. I have a textbox and in the control source I have put, ( =[Forms]![Tests]![CountofProper] ) This does not work as it gives #Name? for each product of the report.

Anymore help would be greatly appreciated.

Thank you,
Tim

 

[randysmid]

Hi,
First, you can have a list box where the user can select "Proper" or something else. That way, you only need one form to take care of this.
This piece of code needs to go on the Criteria line in a query:
=[Forms]![Tests]![CountofProper] 'well, maybe not!!!

ACTUALLY, if I understand this correctly, you are getting the total count of all the "Proper's". Is this correct? If so, then you should probably use the DCount function. I wrote an FAQ on DSum, which works exactly the same as DCount. See faq703-3066

HTH,
Randy Smith
California Teachers Association

 

[tsp120]

Ok, you are helping me very much. But naturally I keep running into problems. Because I am unfamiliar with this DCount function I am not able to get it to do exactly what I want yet, and maybe you can give me some direction.

I put the function in and got it to successfully count the number of occurances of Proper. I did that with the following statement:

=DCount("[Function]","Tests","Function = 'Proper'&quot

But, naturally if gave me the same result for each product. What I am looking to do now is have the counting be done only for the current Product being looked at. I figure something like the following should work, but it does not.

=DCount("[Function]","Tests","Function = 'Proper'" And "ProductID = [Reports]![Report1]![ProductID]&quot

Hopefully from the above you can tell what I am trying to do. Maybe it is even just my syntax. I can also forsee a problem that I will have after the above is solved. The next thing I have to do is only count the 'Propers' for each mode of each product. In the 'Tests' table I have a TestID, and in a different table I have the TestID along with what kind of test it was. (Note that each mode corresponds to multiple TestID's.) How will I be able to count this information from multiple tables?

Thanks so much again,
Tim