Crosstab Query with Parameter (2000)

Topic

New Reply

I have a query that is a totals query (See attached).
For one of the fields I have in the criteria field – Like “*” & [Sic Description] & “*”.
I want them to be able to put something like “Plastic” and get everything that has “Plastic” in the Sic Description field.
I want to use this query in another query (see attached) and make it a Crosstab query. Is this possible to do? The way I have constructed my crosstab query isn’t working with the parameter. I get an error (see attached). Is there any way to use a parameter with a crosstab? Thanks for your help.

Reply | Quote

Replies

It usually helps to declare the parameter explicitly:

Open the first query in design view.
Select Query | Parameters…
In the Parameter column, enter [Sic Description] exactly the way it is in the criteria line.
In the Data Type column, select Text.
Click OK.
Save the query.

Reply | Quote

Thanks Hans that got me past the first problem.
I am using the crosstab query for a report.
When I run the report the parameter comes up. I enter “Plastic” and then another parameter comes up and I have to enter “Plastic” a second time.
If I do not enter anything the second time the parameter comes up, I get everything.
Entering it twice gets me the correct report but is there a way to get it to only ask once?

Disregard – I don’t know what I did but two parameters do not come up now.
Thank you for your help….

Reply | Quote

Hans – I have to eat my words.
Both queries work fine.
When I use the crosstab query to make the report, I get errors.
For some reason when I enter “Plastic” the report does create even though sometimes the parameter comes up twice (It reverted back to that behavior)
I get the following error when I enter “Auto” for instance
“The Microsoft Jet Database engine does not recognize ” as a valid field name or expression
Just running the query works fine but when I use it to create a report it goes bananas.

Reply | Quote

A common problem when using a crosstab query as record source for a report is that the number and names of the fields (columns) may change. A field that was present when you designed the report may not be available when you run it later on.

If the total number of possible fields is limited, you can specify them in the Column Headings property of the crosstab query (list them, separated by commas).
Otherwise, you’ll have to use VBA code to make the report dynamic. This is far from trivial, but it can be done. See the thread starting at post 365323. My first reply contains links to some posts about this subject, and I attached a demo further down.

Reply | Quote

Thanks Hans
Putting the headings in did the trick. I only had three headings.

Reply | Quote

Hans,

I am having the same problem with a crosstab query I am building based on a parameter query that asks for a start date, end date and Ops Group. I get a similar error message, The Microsoft Jet Database engine does not recognize ‘[Start Date]’ as a valid name or expression.

I set the parameters as you instructed, but the original query pops up an additional parameter box with the Between [Start Date] And [End Date] and then pops up the actual start and end date boxes. If I ignore the first box and plug the dates into the 2nd and 3rd boxes the query runs fine.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

(Edited by charlotte on 28-Jul-05 20:38. )

For your own privacy protection, we encourage you to refer to your profile rather than posting your email address.
Also, please remember Rule 10

Hans,

I can’t get it small enough. Is there a way I can email it to you? You can respond to me directly if you would like.

Thanks

Thom

Reply | Quote

If you follow the instructions in the post I referred to, you should be able to reduce the size far enough.

Reply | Quote

Sorry, I missed the step about compacting the DB. That worked.

Reply | Quote

You have to declare your parameters as follow:

Reply | Quote

Thanks, I didn’t understand that Start Date and End Date had to be on separate lines. I thought the parameters were supposed to be the same as in the original query. This worked fine.

Now I need to figure out how to get the crosstab query to drop zeros in the blank fields for each location that doesn’t have a listed issue incident in a particular month. How do I go about that?

Thanks again.

Reply | Quote

Open the crostab query in design view.
Select the Total field.
Open the property window ( View , Properties or the Properties button)
In the format line, enter :
#,#,0,0
The first # determine the format when the number is positive
The second # determine the format when the number is negative
The first 0 determine the format if the number is 0 (zero)
And the second 0 deternine the format when the number is Null. What you asked in your question.

Reply | Quote

I must be doing something wrong. I open the query, select the Total field by clicking on the top to select that column in the grid and open the Properties dialogue. I try to type the format you recommended in the format line but it won’t let me type a comma between the two zeros. I get
#,#,00.

What am I doing wrong this time?

Thanks for your help.

Reply | Quote

What if you use semicolon ?
#;#;0;0
I never know when to use , or ; (It is different between US and International versions of Office)

Reply | Quote

As far as I know, the US version uses semicolons too. The comma acts as thousands separator.

Reply | Quote

Ok, so for the format function they are the same.
But if you build a function in a textbox or a query, are the comma and the semicolon inverted in use in the International version versus US version ? Or did it depend from the Windows Regional settings ?

Reply | Quote

In expressions, you must use the list separator character specified in the Regional Settings control panel. In the US, the comma acts as list separator, and in Belgium and The Netherlands, the semicolon is the default list separator. Example:

US: =DCount("*", "qryProducts", "CategoryID=3")
Belgium/Netherlands: =DCount("*"; "qryProducts"; "CategoryID=3")

Because the comma is either the thousands separator (US) or the decimal separator (Belgium/Netherlands), it can’t be used as format separator; therefore semicolon is used in both. Example:

US: #,##0.00;(#,##0.00)
Belgium/Netherlands: #.##0,00;-#.##0,00

Reply | Quote

Thanks for the clarification.

Reply | Quote

The semicolon worked great.

Now I have to build a dynamic crosstab report. From what I have read in the threads I looked up under dynamic crosstab reports there is no short cut to do this. My problem will be the number of locations we have versus the actual number of locations that will be included in the output from the crosstab query. I.E. 51 actual locations and perhaps 10 – 12 that would appear in the crosstab each month based on the issues that get reported.

Do I need an unbound text box on the reports design page for each location, or can I simply assume that there would be less than 15 locations reporting each month and only include 15 unbound text boxes?

Thanks again, you guys are the best.

Reply | Quote

Go to rogersaccesslibrary for a sample database with a dynamic report on crosstab queries.
DownLoad Page

Reply | Quote

OK, I get how to set up the blank report in the design view. But since I am not a programmer, although it is starting to look like I will need to learn at least enough VBA to survive, I am not sure what to modify in the code besides the number of columns I am working with and the name of the original crosstab query, I am getting runtime error messages that I am not sure what to do with.

Reply | Quote

Creating a report based on a crosstab query is not an easy thing. It involved a lot of code and understanding vba. If you post a stripped version of your db, maybe someone can help you with your situation.

Reply | Quote