QBF/Query on report

Hi folks,

I have numerous reports that are based on different queries.   What I am 
looking for is a way that if a user wants to pull a certain report, that when 
this report is selected from a form, the user has the option based on 
different fields to drill down the information being returned in the report.

For example, one report would give details of all invoices to be paid 
(considered incomplete).

I want to give the option to the user to select the from and to dates that 
the invoice was received for example.

Or again, the user may want to search for all unpaid invoices that belong to 
a particular fund center (another field).

The user may want to select one or more invoice types.

These are all high level examples of what I want to happen.

I want the user to select the report that he/she wishes to have, and then 
based on this report be able to narrow down the amount of data that is 
returned by these option.

I have a query that makes a table called "tbl_reports" that gives the list 
of the reports.

I have a dropdown box on this form that gives the available reports.

when a particular report is selected, numerous search options would show on 
the form.  No problem here.

Then the user inputs its criteria and then selects to run the report.  

What is the best way to do this, so that this becomes very user friendly for 
the user.  These users have very limited knowledge of access so I can't 
exactly ask them to go into a query and change the criterias....they'll screw 
everything up....

If I can somehow get into the report's query and change the criterias based 
on the fields on the form mentionned above, but that once the report is ran 
and close, that the query defaults back to its original, I'm ok with that, 
howevr I'm not sure how to go abouts getting something like this to work.

Looking forward to responses, I know this is kind of vague, but pplease ask 
questions if you need more info, otherwise I'm just trying to get a feel as 
to what I will need to do to make this work.
0
Utf
8/22/2007 4:26:02 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
835 Views

Similar Articles

[PageSpeed] 24

Jean-Francois

I use this "Order Reports" approach myself.

On the form, I enable/disable selection criteria controls based on which 
report is selected.

Then, in the <Print Preview> command button code, I dynamically generate a 
WHERE clause using the controls that are  1) enabled, and  2) have 
selected/entered values.  The <Print Preview> button code then uses this 
WHERE clause in opening the selected report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Jean-Francois Gauthier" <JeanFrancoisGauthier@discussions.microsoft.com> 
wrote in message news:A909B1C2-A17D-4BCF-BA49-3884DA01439C@microsoft.com...
> Hi folks,
>
> I have numerous reports that are based on different queries.   What I am
> looking for is a way that if a user wants to pull a certain report, that 
> when
> this report is selected from a form, the user has the option based on
> different fields to drill down the information being returned in the 
> report.
>
> For example, one report would give details of all invoices to be paid
> (considered incomplete).
>
> I want to give the option to the user to select the from and to dates that
> the invoice was received for example.
>
> Or again, the user may want to search for all unpaid invoices that belong 
> to
> a particular fund center (another field).
>
> The user may want to select one or more invoice types.
>
> These are all high level examples of what I want to happen.
>
> I want the user to select the report that he/she wishes to have, and then
> based on this report be able to narrow down the amount of data that is
> returned by these option.
>
> I have a query that makes a table called "tbl_reports" that gives the list
> of the reports.
>
> I have a dropdown box on this form that gives the available reports.
>
> when a particular report is selected, numerous search options would show 
> on
> the form.  No problem here.
>
> Then the user inputs its criteria and then selects to run the report.
>
> What is the best way to do this, so that this becomes very user friendly 
> for
> the user.  These users have very limited knowledge of access so I can't
> exactly ask them to go into a query and change the criterias....they'll 
> screw
> everything up....
>
> If I can somehow get into the report's query and change the criterias 
> based
> on the fields on the form mentionned above, but that once the report is 
> ran
> and close, that the query defaults back to its original, I'm ok with that,
> howevr I'm not sure how to go abouts getting something like this to work.
>
> Looking forward to responses, I know this is kind of vague, but pplease 
> ask
> questions if you need more info, otherwise I'm just trying to get a feel 
> as
> to what I will need to do to make this work. 


0
Jeff
8/22/2007 6:06:36 PM
Reply:

Similar Artilces:

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Report Format
Env: CRM 3, VS 2003, remote connect to CRM server. I've created a QUOTE in VS2003 and when viewed using PREVIEW from within the VS environment it produces a beautiful QUOTE (at least in my opinion). When printed, again from the VS environment, it is perfect. When I upload the RDL file to the CRM server, create an new REPORT using this RDL then produce a REPORT, the formatting is all over the place. When I inspect the source (in VS) there are no fields that extend past the defined size. It appers that the CRM report engine randomly adds CRLFs all thru the report ... ??? ...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Payables Trial Balance Report with Options
I am trying to run an A/P Trial Balance Report today that will give me the A/P balance at January 31, 2007. I keep getting the balance as as February 28, 2007. I've run the same report for A/R and am able to get the balance as at January 31, 2007. Currently using GP 9.0. With my previous employer who was also using GP 9.0, I sure that we were able to run an A/P Trial Balance report for a previous period. Does anyone have any suggestions as to what I my be missing or what may be set up incorrectly? -- JR JR, The only date that you can spcify on the Aged Trial Balance With Options, ...

Display Report Header/Footer
Can somebody please give me the telephone number of the smartass MS VP who came up with the bright idea for the Ribbon so that I can call her and have her tell me how to display the Report Header & Footer in a report? Obviously, I must be an idiot because I can't figure out how to do it with this simplied, intuitive, help-ya out m***** fu***** ribbon. Oh stupid me, its under 'ARRANGE'. How idiotic of me to think that it'd under 'DESIGN' since I'm 'designing' a report as opposed to 'arrangeing' a report. "microsoft"...

Cannot delete report
Hi All, I have install the report manager sometime ago, it is working fine. Until recently we found that when i click the action menu, nothing is come out. I can't delete or rename the report. But I can add new report. Do anyone encouter this before? Thanks. -- Regards, Venedict ...

Log ON Report Manager
Install Report Manger in the Server CRM Satisfactorily, but I have problems when I do log on to Crystal APS, I am using an account of domain administrator and it does not allow me to accesar to the APS. APS User Name: CCSGR3000\crmadmin APS password: ********* APS Server:servcrm I need Help, Thanks. The problem is with the login. Use the following: APS User Name: administrator Password: (should be blank) APS server: localhost (if it's local) I did run into a problem that Report Manager couldn't be accessed from anywhere on the network. It was an IIS configuration problem. &q...

Crystal Report not found on File Repository Server
I recently upgraded our MSCRM server to a new server. We don't typically use the internal CRM reports, we have some custom ones created with PowerTrack that we primarily use. The CEO tried today though to open one of the canned MSCRM reports and got this error message. I tried the others and I get it on all of them. CrystalReportViewer File frs://Input/a_063/021/000/5439/6ac34043c58b1112.rpt not found on File Repository Server. I checked the input folder in c:\program files\crystal ent 9\filestore\input and it is empty. I would have copied them from the old server, but it is ...

eXtender and Crystal Reports
I've created a Crystal Report to print eOne solutions eXtender fields. They store inaformation in 4 different tables: EXT00101 (String), EXT00102(Numbers), EXT00103(Date),EXT00104(Time).. I have created a View that unions all these tables - and convert them into a Varchar.. Once that is done, I've created a function called GetExtFieldValue where i pass the extender window id and field number and it returns the value for that string. This report was working fine and all of a sudden it became so slow that now it takes 7 minutes to get one record. If I delete all the data from EXT...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

Calculate Subreport totals in a main report Group footer
Hi all Apologies if this has been answered before but I can’t find it. I have a main Report with a Group called “Product_Category” which lists a number of “Products” in the Detail I have a Subreport named “product_costs” which has a record for each date and Text Boxes named “materials” and “fuel” (there are more but I’ll keep it simple). The Subreport sums all costs and has Text Boxes named “summaterials” and “sumfuel” in the footer (all with a height of 0.1cm) The Subreport is embedded in the Detail of the Categories and linked by Product_id In the Detail of the Main Rep...

EDC Reports
Is there a way to gain access to the EDC report(s) in SO Manager without having to configure it for EDC? I need the accounting department and back office mangers to be able to look at and print these reports without them actually having to or needing to do any EDC processing. You could use the Tender Summary report (Reports | Misc | Tender Summary) to get credit card totals by type & register. Marc "Alex" <Alex@no.com> wrote in message news:edLaU8DUHHA.2212@TK2MSFTNGP02.phx.gbl... > Is there a way to gain access to the EDC report(s) in SO Manager without >...

Difficult query
Hi, I have a table called WT,contains the fields "Type of call","DateW" and "ID", this table is used to by users to add rows that determine type of calls received in a call center,I want to create a query with the following criteria: 1- To view number of calls received in each type per day. 2- To show the field "Type of call" in this query,even the type that wa not used,and to view number 0 in the count field. 3-Prcentage of each type of call . On Dec 11, 3:52 pm, Pietro <Pie...@discussions.microsoft.com> wrote: > Hi, > I have a tabl...

Report Query in RMS
Is there somewhere that I could get some information as to how the reports in RMS are composed. When you look at the various reports they all seem fairly simple in there basic structure. Are the any examples of how these reports are composed. The //--- Columns ---// iformation seems to be the same in most reports, same as the //--- Title Rows ---//. Setting up the flitters and the TablesQueried I need to understand. I have the schema for 1.2. How do I better understand the following or what do I need to learn. I wish I was in college again and had all the time in the world. //--- Repo...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <MikeW@discussions.microsoft.com> wrote in message news:...

Exporting Daily Z-reports from MRS to Quickbooks
Hello all, Basicly im trying to figure out how to export the daily Z-reports from Microsoft Retail Systems 1.2 to Quickbooks (I believe 2005) in 3 steps or fewer. Ideally, there should be a way to do this without exporting the data to a file, but rather do it directly from MRS after posting the z-report. If anyone can help please let me know if you are using the US version of Quickbooks, the ability to post directly from RMS to QB is built in - you just need to set it up and assign your accounts. Ask your RMS reseller and Accountant/Bookkeeper for help - usually takes about 2 hours t...

VBA and SQL Query
hi, i use GP 8.0 and i want to make a custom form for a specific need. i cannot use Dexterity for this change so i really need to use VBA. however in the vba form i want to make a few sql select queries to pull client names, and call a table view, and eventually run a few update queries. is that possible to do with VBA in GP 8.0 ? Yes. You need to create an ADO connection and execute through that. Go to Customersource and search for ADO and VBA. Somewhere, I think in one of the demo links, is a file containing sample code that show you how to do this, including use of the retrievegl...

Sum of each grouped item in a report
I have a form that logs time spent on auditing activities for the Internal Auditors in my company. Each time the auditor performs an audit related task, they enter a new record under their name. This means they could have many occurences of the same function. I then created a query/report that breaks down the different auditing activities for each internal auditor. Management has now asked for a report that gives a total for each activity for each internal auditor (example - John Smith: Audit Prep - 2.25, Follow-up - 3.75, Audit - 7.50). I want to figure out how to do this on a rep...

pass through query variables
Hi Guys, I am looking for some help with a pass through query. I have a number of reports that allow users to select values from combo box's and want to know how I can pass this data in a pass through query. I have looked through and searched but seem to just get sites that I have to register to get some details, but do not wish to register elsewhere to get some info. If any has an example or links to any sites that would cover this I would appreciate it. An example of what I would want to pass is [forms]![frmReports].[txtsupplierID] [forms]![frmReports].[txtprod...