Table has a single field (AllocAmt) that is repeated for each detail transaction. Therefore I can't put the sum of the single field on the report because it adds that sum times the count of the detail.
The solution I attempted was to create a subreport using a summarized query then divided the summarized AllocAmt by the count of the same field and got what I wanted. It worked beautifully. I named the calculated field and then created a sum of the new field (SumAllocAmt) in the subreport report footer. I ran the subreport and my totals came out fine.
Then I added the subreport to the original report and the information for each grouping came out fine.
Now my problem ... when I call the field ('=srptAllocAmt.Report!TotalSumAllocAmt") I created in the subreport report footer it does not give me the total for all groups in the original report. In fact it gives me the last record amount instead of a total. When I enter the expression "=Sum([sprtAllocAmt].[Report]![TotalSumAllocAmt])" and try to run the report a box pops up requesting a "Parameter Value" for the field sprtAllocAmt.Report!TotalSumAllocAmt. Why doesn't it recognize that I want a sum of the field contained in the subreport? I've gone to the properties box in all the subreport fields and made the data available "overall". I don't know what else to try. Any suggestions?
I want to build a form that allows a user to book an appointment.
I want them to be able to see a choice of their own or other people's appointments for the day so they can choose a time.
I'm told i can build a query to show the appointments and put that in a subform on the form.
My question is this: to select which person's appointment to display and which day, i need to give the query parameters. I don't really want to do it with the parameter query popups. What i'd like is a couple of pull down lists at the top of the timetable to select and display the person and date.
Is this possible? Should i put the pull down boxes on the form or in the query?
I have a project that is made up of one main database supplying data to two other databases. Normally it works fine. I have installed it on some PC's belonging to our companies agents and in most cases it works fine as well. However, in some cases when one of the smaller databases is opened, often several days after a successful install, the user is prompted for a password (there is none attached to the database). Obviously the database will not open without it (the password). In these cases I replace the database with a copy and it works fine again. I have read in the forum that similar problems have been caused by a problem with the System.MDW file and that it should be replaced with a clean copy. I am not clear on this point. Is my copy clean and they have a faulty one or am I inadvertantly creating the problem on their computer. As I said before it works fine in our office and most others. Just one or two have this problem. Any suggestions on how I could fix it please?:confused:
Is there a way I can display my parameter in my report? I have a report that's generated from a parameter query. In a control I want to show what the parameter values are: Between [start date] and [end date]
Is there any way to avoid the error code when using the date parameters in Report header when there is no data for the specified range. The following are the parameters being used to get the date range. They work fine as long as there are records in the date range entered.
=Reports![Toys and Adapted Equipment Centre Signed Out By Date Range]![Enter Start Date]
=[Reports]![Toys and Adapted Equipment Centre Signed Out By Date Range]![Enter Final Date]
I have a report based on a query with a between two dates parameter (Begin date and end date). This parameter is fed from a form. All works well - query, form and report. My question is can the date parameter appear on the report header so users know the report was based on a start date of 1/1/2008 and an end date of 1//2009 for example?
I need to insert the parameter dates for a query into a report. I have tried all the methods I can find but none seem to work. I think its to do with the way my queries are structured.
I have a basic query (q1)! Based on this is a grouping query (q2)! Based on this grouping query is my report. I have set up a form prompting for the parameter dates. This all works ok and my report displays the correct data (between the dates entered). The problem is that I cannot get the report to show the parameter dates.
I have a report which asks for the beginning date and ending date which they want. The report is generated using this information. I want to put the information entered in the parameter request into the heading of the report. How do I recover this information to put it in.
I have a database with which I use to generate reports as pdfs to load to a website.
However when I go to save the file as a pdf it is always as the report name.I am trying to automate this by using a macro as I need to run a report 50 times for ships with a different parameter each time (name of the ship) Another report needs to be run about 30 times, this time the parameter being a date And finally two other reports, but I can live with those being the name of the reports.
Firstly can this be done?
Secondly which would be the best way?
I've tried using the macro route by I run my Report "Main_Ship" with a where clause of [ship]="wiltshire". This produces the report with the relevant ship, but I am then unsure what to use next to save it as a pdf with the name "wiltshire" using the macro route.
or
Create a VBA routine that runs the report as many times as required each with a different parameter for the name of ship or date.?
Ok I saw this posted up in the forum but it was not explained.
I have a Query which asks for a parameter before giving an output.
I have a report that uses this query to display the information.
Ok what I am trying to do is create a form that has a textbox and I enter the parameter's information in that textbox or textbox. How do I send the information to the query?
The reason for such a thing is people tend to not follow the set guidelines for putting stuff in a parameter box. So what I decided to do was set up a calendar form which a person has to physically choose the date from the calendar and this leaves no room for real error. My problem is I can get the calendar information to the textbox or textboxes but can't figure out how to code the button to send the textbox information to the query & parameters & report, etc...
Can someone please help me out of this situation? or point me to a starting point?
I am trying to add another parameter to a report to only get those specific records. I did not write the code and am very confused on how it works. Right now it is getting records in the Access database between the 2 dates entered. But NOW I need to add a parameter to select only records between those dates AND with the AccountNumber LIKE acctltr (this is the field from the form). They can either put in an "X" or an "P X". The AccountNumber needs to end in which ever one they enter.
Here is the code that is currently existing and supposedly works. At least it gets all the records between the dates even tho it still prints records with a ZERO balance.
Code: Private Sub cmdprint_Click() On Error GoTo exit_cmdprint 'mysql = "SELECT * from InvoiceTable " & _ ' " WHERE ((not InvoiceTable.InvoicePrintDate1 Is Null) AND (not InvoiceTable.InvoicePrintDate2 Is Null) AND (InvoiceTable.InvoicePrintDate3 Is Null) AND (InvoiceTable.InvoiceDate Between #" & dtefrom & "# And #" & dteto & "#))" 'Me.RecordSource = mysql
So I have a report that opens via Parameter. The SQL behind the query that runs the report is (I took out alot of lines that aren't necessary to answer the question)
PARAMETERS [Enter Your Box Id] Short; SELECT DocumentsTable.OrganizationalID, . . . DocumentsTable.Status FROM DepartmentsTable INNER JOIN (Year1 INNER JOIN DocumentsTable ON Year1.ID = DocumentsTable.RecordDateYearID) ON DepartmentsTable.ID = DocumentsTable.DepartmentID WHERE (((DocumentsTable.Voided)<>'Y' Or (DocumentsTable.Voided) Is Null) AND ((DocumentsTable.ID)=[Enter Your Box ID]));
So if I click on the report, I get a little popup that says "Enter Your Box ID", and when I do, the report works just as expected.
However, I also want to be able to open this report via link from another report, and pass the Box ID #. I just can't get the syntax right. I would have thought it was this:
Private Sub ID_Click() DoCmd.OpenReport "Find A Box", acViewReport, , "[Enter Your Box Id]=" & Me.ID End Sub
However when that execute, I still get the pop up asking for "Enter Your Box ID"
I have a report that pulls data from a crosstab query. The report works perfectly and prompts for a "StartDate" when it is run.I need a form with a date field that can be selected. Then a command button which when pressed opens the report with the selected date passed as the parameter.the code I have so far is in the on click event of the button:
I was hoping that this would pass the txt.startDate field on the form to the report's "StartDate" when it is opened, but it is still prompting for the parameter when the report loads.Should I be using openArgs rather than the where clause? Or do I need to configure something in the "on load" event of the report also?
I have parameters set in a query. This query generates a report. I want to have the parameter field the user enters show in the report. Example: I have Region set up as a parameter. The criteria in the query is Like "*" & [Region] & "*" The problem I'm having is in the report it only returns what the user enters in the parameter prompt.
For Example: If I type south it only shows south rather than south east or south central (which is the whole field), etc. on the report. It shows in the table generated by the query but not on the report. If I leave the parameter prompt blank it brings back all the records like it should but again leaves the "region" field on the report blank.
I would like to display the value of my parameter query into the title of the report.How could I do this on Access 2007?So far I have made another field in the query and called it ParaDate: [JobDate]
I have a couple of questions which are sort of related. They both revolve around Parameters with a Query/Report.
Here is a bit of background. The source Table is formatted in a way similar to this:
Code--Account#--Expense Description--Jan--Feb...
When running the Query, and associated Report, the user is prompted to input an Account# via a Parameter that I set up. The dialog box pops up with the word "Account#" and a box for the user to enter by what account that they want to restrict the Report. [Question 1] - Is there a way to make this dialog box contain a drop-down box with a list of predetermined accounts instead of the aforementioned entering of an account number? If not via this dialog box, is there another way?
Also, multiple accounts roll up into a single P&L line item. For example, let's say that accounts 1234 and 5678 both make up "Equipment". [Question 2] - Is there a way to run the Report in such a way to return all accounts which roll into a line item? I want to have the ability to run by either an individual account or the P&L line in total.
I am running an Access 2003 report that outputs to an Excel Spreadsheet The parameter query has two paramerters First Date and Last Date. The report runs from an Button OnClick event. I need to include the two dates in the 'name' of the spreadsheet as below
Private Sub btn_report_between_dates_Click() DoCmd.OutputTo acOutputQuery, "qry_all_calls_between_dates", acFormatXLS, "Calls By Between Dates " First Date" and " Last Date" - Date Report Run " & Format(Date, "dd-mm-yyyy") & ".xls", True End Sub
(btw I know it is preferable to use the TransferSpreadsheet method, but I've not got around to that way yet)
I have one form which have two multiselect listboxes. They work, but every time when I choose id_organizacija from listbox popup enter parameter value comes up.
I am already tried to put id_organizacija in [ ] but that didnt solve problem.
Code: Set ctl = Me.lstEmployees For Each varItem In ctl.ItemsSelected strWhere = strWhere & ctl.ItemData(varItem) & "," Next varItem Set ctl = Me.lstOrganizacija
I have a parameter query for looking up a specific lot number in my database. The lot number table has 4 related material tables that deal with material issued, reworked, and rejected for that lot number. They lot number table is in a one to many relationship with the related tables.
I have a report based on this parameter query with 4 subreports. I want the report to display the lot number data and sub reports to display the related data in the material tables (all linked by the lot number defined in the parameter)
