Queries :: How To Use Criteria Or Parameter In Crosstab Query
Oct 5, 2013
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"..
I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading [Plan Type]- Group By / Column Heading [Pmt Amt]- Sum / Value [Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
It is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.
For instance you may want Access to return all records that begin with the letter Q.
To do this you need to enter the following expression into the criteria row:
Like [Find records beginning with:] & "*" Or Is Null
The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.
By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.
But how do I search if i want all the records with John anywhere in the field???
I had an issue with writing LIKE statements in query criteria yesterday [URL]....
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code: SELECT tblPeople.name, tblPeople.num FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name) INNER JOIN tblPets ON tblPeople.name = tblPets.name
[code]....
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
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 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 am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I am trying to create a crosstab query, but can get it right.
Table1 = input Table __________________________________________________ ___________ name StartDate date Present A 1201 1201 P B 1202 1205 P C 1203 1206 P D 1204 1207 P E 1206 1209 P
Can get this result with Crosstab Query;
TRANSFORM NZ(First([Table1].[Present]),"a") AS FirstOfPresent SELECT Table1.name, Table1.StartDate FROM Table1 GROUP BY Table1.name, Table1.StartDate PIVOT Table1.date;
__________________________________________________ ____________ name StartDate 1201 1205 1206 1207 1209 A1201 P a a a a B 1202 a P a a a C 1203 a a P a a D 1204 a a a P a E 1206 a a a a P
But am looking to add NA for StartDate > Header Date(1201,1205,1206,1207,1209). See below. Does anyone have a solution or an alternate method
Looking to get this type of results __________________________________________________ ____________ nameStartDate12011205120612071209 A1201 Paaaa B1202 NAPaaa C1203 NAaPaa D1204 NAaaPa E1206 NANAaaP
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?
Is there are way to add print time criteria to a CrossTab query? Everytime I try I get a message "The Microsoft Data engine does not recogonize [Forms]![FrmSalesCategoryByMonth]![Period] as a valid field name or expression". Any ideas?
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
In MS Access 97 I have a basic query based on a single table. When I use "Between [Start Date] and [End Date]" as the criteria in a date field it returns incorrect records.
It works correctly on date fields that are not calculated but not on the date field that is calculated. If I type in the actual dates in the criteria (instead of the prompts) it returns the correct records.
Is there a solution to this behaviour? I need the users to be able to input the dates.
Thanks.
P.S. I have tried using the base query as a subquery but I still have the same issue in the second query based on the subquery.
I have an append query that contains a parameter [PTIdent] under the field named PTID. I would like to be able to set the parameter through VBA from a control on a form eg Form!PTID
I use the following code as normal Private Sub Command19_Click() On Error GoTo Err_Command19_Click
Is there a way within this code to do this? I realise I could set the form criteria within the query itself. However I wanted to be able to use this query from multiple forms.
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
I have a report that is fed by a parameter query. When a command button (Command23) is clicked a parameter box opens and the user enters a subsystem number. A report for that subsystem then opens.
I want to open the report with VBA and show a message if the user has failed to enter a subsystem number in the parameter box and just clicked OK.
The problem is I don't know how to refer to the parameter box and detect if it has been left empty before clicking OK. I first tried to use IsNull in an If statement with Command23.Value, but now realize it is the value in the parameter box I am after, not the "value" of Command23.
How can I change my code to detect if the parameter box has not been filled out?
Private Sub Command23_Click() If IsNull(Command23.Value) Then MsgBox "You did not enter a Subsystem", vbOKOnly, "No Criteria Entered" Else DoCmd.OpenReport "rptTESTInfoBySubsystem", acViewReport End If End Sub
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable SELECT QryTable01.productName FROM QryTable01 GROUP BY QryTable01.productName PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable] t Then the report row with the values:
Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?
Code: PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime; TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM] FROM Query1 WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null)) GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp] PIVOT Format([Date Stamp],"Short Date");
I want to show this in a report, similar to an spreadsheet, with:
ProductionForecastProductId = Rows ProductionForecastMonth = Columns ProductionForecastQuantity = Data
I thought of using a crosstab query like this:
Code: TRANSFORM avg(tblProductionForecast.[ProductionForecastQuantity]) AS AvgOfProductionForecastQuantity SELECT tblProductionForecast.[ProductionForecastProductId] FROM tblProductionForecast GROUP BY tblProductionForecast.[ProductionForecastProductId] PIVOT tblProductionForecast.[ProductionForecastMonth];
but I don't want the average of ProductionForecastQuantity. I want the actual value!
Is a crosstab query the wrong method to use?
If no, how do I show the actual value of ProductionForecastQuantity
I have a crosstab query that I would like to sum data across. Each row is a seperate experimental product, and each column is a test phase that each product goes through. The data is the number of days each product was in each test phase. What I need is the sum total of days that all products were in all phases. Think of this table as a single item, and each row is a slightly different version of the item to test. So what I need is essentially how many days this item has been in testing across all variations.
