I have searched and searched for a thread before posting this, there are some discussions on related issues, but I could not find a solution. This is my problem:
I have a table with Incident_Type (a total of 6 selections from a combo-box), Incident_date, Incident_SubArea(25 selections from a combo-box), a check box for IsStaff_reported and all these are recorded by Incident_ID.
I have created a monthly report based on a parameter query that prompts for start and end dates using a custom form for incidents.
Now I need parameter (again start and end dates) cross-tab queries for number of incidents (i.e. count of Incident_ID) that are 1. IsStaff_reported, and 2. Not IsStaff_reported. to show up in a single report.
I dont know if that is too much to ask for or is there a simple way to accomplish this. Any help is appreciated.
Im trying to create a crosstab query with parameter entry from a form.
I wish to be able to specify a date (using Microsoft DTPicker control) and a shift (using a combo box to the relevant table) to make the selections.
These two selections on the form are the criteria for the cross tab query which returns the downtime totals for each category for each line.
I have defined the parameters for the query by putting which seems to work for most people from what I've read, and has worked for me on previous forms in the same database (although not in parameter crosstabs....this is my 1st parameter crosstab)
However, I still get no result from the query. Without the parameters, the crosstab query works fine.
Help please...I'm really stuck. Here is the sql for the query, can anyone see anything that is obviously wrong with it?
I have a table that has three columns: Nationality, Gender, and Date. First I had the problem of grouping the Nationality column by Gender. That i resolved by creating a crosstab query. Now, I want to put a criteria on Date field so that when the query is run it will ask for the date input and will only show the Nationality and Gender result for specific dates. But when I write something in Criteria field it shows an error saying: "The Microsoft Access database engine does not recognize "[Registration Date]" as a valid field name or expression"..
TRANSFORM Workersdetail.workername AS CountOfedate SELECT Workersdetail.[attendance], Count(Workersdetail.[edate]) AS [Total Of edate] FROM Workersdetail GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter] PIVOT site+Cstr([workerhourenter])
I wanted to know that, is there any option through which my report gets autoupdate or refresh incase of addition in SITE field (as mentioned with PIVOT)?
I've got a self updating crosstab query, its essentially a monthly summary and every month a new column is added (one corresponding to the current month, i.e., next month the new column will be may, following that the new one will be june, etc)
I've designed a report to be based on this query and i tested it out by manually adding data for next month into a table, the query auto updated however the report remained the same (ended in april instead of adding a new column for may).
Just curious if there is a way to automatically add these new columns to the report every month or will i have to do so manually?
I have the following database, in my query I am trying to do a calculation as the value parameter within the corsstab.
Ultimately I an trying to get month as the Row Header (from date) and Category as the column header. The Value inside the table I am trying to calculate is A/(B-C-D)...
I have a report based on a crosstab query with a subreport based on a second crosstab query both the crosstab queries have the same 3 parameters (2 as string, 1 as boolean) which I pass through a dialog form that closes after the report is opened (in preview).looks like everything works fine at least for the first page of the report but when I try change page, it prompts me to re-enter the parameters. Of course, if a do not close the parameters form I will not have this problem but that is a popup dialog which I do not want to close manually after the operation is done
So I tried to pass the form input first to a Public variable then to a Public Function but I was not able to reference neither of them in the PARAMETER statement of the 2 Xtab queries..As a function, each of the following declarations prompts for input at runtime::
MyFunction, [MyFunction], [MyFunction()], [=MyFunction()] while the following are not accepted (syntax error) MyFunction(), =MyFunction()
I have created a stored procedure parameter query and using access created a report that runs the procedure and creates a report based on a parameter entered:
1) I wanted to know if I can specify a default paramter so if I do not input it returns all records?
2) Can I create a stored procedure so it asks for month, for example if I had a createdDate field of data type datetime and wanted to return records for a specific month?
I am trying to use Dcount to find the number of records in a query and put it on a text box in the header of a report. The query is a parameter query. The button opens a form which asks for start and end dates, the query reads those dates from the form and the report displays the correct number of records that fall within the date range. The query is based on another query, which is based on the table. I tried =dcount("*","qryName") but I get #error. Maybe becauase of dates? or parameters?
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:
In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:
[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date] and also, [Forms]![FrmDate]![Enter Type of Audit]
On each subreports On Load Event, I have added:
Private Sub Report_Load() Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#" Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#" End Sub
and I've set the Filter On Load property to: Yes..I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, I have 6 blank subreports in report Preview. The headers are showing up but none of the data.
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit
I have 12 months' worth of data that the user wants displayed on a crosstab report. I have no problem creating the crosstab query or subsequent report. However, the user wants to be able to select their own 12 month time period. So, my crosstab is based upon a make table query, that will allow the user to enter parameters. Now, my problem is that these 12 month column headers/data will change as the user enters different date ranges. How do I write the code that will allow me to pass these variables to the report? I'm using 97. Thanks!
I have a crosstab Query as the source for my report, of course the issue is the column headings on the report. I have Purchasers as a row heading, Year as a row heading, Month/Year as a row heading, Meter as a column heading, PaidMCF as Value and, a total as a Row heading. My issue is feeding the column headings on my report with the meter names.
Purchaser 1 has 23 meters attached Purchaser 2 has 1 meter attached Purchaser 3 has 6 meters attached Purchaser 4 has 2 meters attached Purchaser 5 has 16 meters attached Purchaser 6 has 11 meters attached.
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
I am playing with a report. Crosstab report works, but I would like to print check mark if value is "X". Other values are "A" and "E" and they need to remain as they are.
I've made a crosstab query and would like to use it to create a subreport. In the column headers I have names of courses. Courses can be added or removed. How can I make a crosstab report with dynamic columns?
Hi. I have a problem but not sure if I can do what I want to. I have 7 daily reports that are based on different queries but want to be able to print all 7 at the same time using only one command button, but I also want to be able to have all seven run using a week commencing date entered into a parameter or something similar when the print button is pressed.
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 have a report with field that has as a control source this value " =[Time Period In Days?]"
This works well as long as the user enters a value and clicks on "OK", however if the user clicks on "Cancel" he gets an error.
I assume that this is because the report is trying to open and it gets a null value from this message box? Is so, how can I ensure that a default value of "0" is automatically entered?
I am trying to create a report, filtered on a combo box(txtname).This combo box is having a list of names.I have one query(mainquery) based on a single table(maintbl).I created a blank form(GenerateReport) with abovementioned combo box(txtname) and a simple button, which will open a simple report.In mainquery, under names column i am defining the criteria:
[Forms]![GenerateReport]![txtname]
Report is being filtered accurately.But when I open query i receive error 'Enter parameter Value'
I have a command button that opens a report.In one of my reports, this macro works:
Code: ="[eEmployeeID]=" & [eEmployeeID]
However, in another command button that should open another report, the code below does not seem to work:
Code: ="[echEmployeeID]=" & [eEmployeeID]
It just keeps giving me errors saying that there's a mismatch, etc. The field in my report that holds Employee ID is named echEmployeeID. If I write this code:
Code: ="[echEmployeeID]=" & "[eEmployeeID]"
it sort of works, but Access asks me to enter parameter value first before executing the where condition. I already tried to change [echEmployeeID] to [eEmployeeID] both in the source table, and the report but it just doesn't work.
I have devolved two reports which receive their data from the same query. I could not fit everything in one report as I am limited by Access’ 22 inch limitation. I would like to enable a user to run the reports off a Macro. The report/query requires the user to enter a “Parameter Value” such as date and facility number. When running the Macro it requires the user to input the same “Parameter Values” twice, once for each report. What I would like the Macro to do is only request the Parameter Values once. Is there a simple way to achieve this? Thanks for any assistance.
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.?
