Crosstab Query and Parameters

[chaostheory]

Evidentally it is one of those "It only needs to be done in this one specific case" type of things...

All my other queries run perfect without declaring parameters or column headings.

Thanks for all insight by both posters!

 

[JamesMcS]

How bizarre - column headings, I think, you can only specify in CT queries. It does greatly increase their speed though.

Have a wild one bud!

 

[boblarson]

So you have put the parameter in the crosstab parameters too?

If you can, can you remove any sensitive data and replace it with test data and then upload a copy of the database?

 

[boblarson]

Never mind. I missed the solution post (am on the bus going to work with my netbook and the screen's not so big).

 

[JamesMcS]

That is pure dedication to the cause

 

[boblarson]

That is pure dedication to the cause

well, not really. It gives me something to do for an hour while going to work instead of just sitting there looking out the window.

 

[JamesMcS]

I'll let you off then.

 

[chaostheory]

So after getting more into it, even though it is a solution it's simply not an acceptable one. I realized after typing in all the column headings, i also have to type in all the column headings that show up in the crosstab portion of the query (not just the ones in the query design view) and there is over 50 test names that span out. So i guess they don't get a crosstab query...it's simply not gonna work to type out 75 fields names, just to run a crosstab query from a form.

 

[boblarson]

I've done up a crosstab query with criteria before and no problem. I did use a query first to select the values using the form (and put the form reference into the parameters area) and then I also put the form reference in the crosstab query parameters even though it didn't even have that field in it but was based on the other query and it seemed to like it.

 

[chaostheory]

Copy of the dummy DB in exactly the same setup as i have it is located here
http://josephknight.net/access/dummyDB.mdb

 

[boblarson]

Okay, here you go. It appears to be working fine now for the crosstab with the parameters. What I did was -

1. Created a query from the table and set the criteria there. I set the parameters to refer to the form's text box.

2. Removed the criteria from the crosstab query and added the form reference to the parameters.

Removed the data source from your search form as it is not necessary.

See attached, revised sample.

 

[chaostheory]

Wait...how...hmmm...er....Ok im lost.

You created a regular query...

qryPhysProperties4CrossTab

But i see no reference to this query anywhere in the form

And when i click the button on the form to run the search, it is running the query i created which magically works now...

So what is the purpose of the regular query and ...where did you ...reference it?

 

[boblarson]

The query I made is actually the source of the crosstab query. Look into the crosstab query and it is no longer the table as the source but the query I created.

The form does not need to be bound to anything. It is just providing a value from the text box to a query.

 

[chaostheory]

Ok so you created a query from Physical Properties, then used the Query as the source for the crosstab, instead of the table. The txtbox provides the search criteria for the regular query, which provides the results which are then put in crosstab format.

freakin genious.

Thanks, it's almost embarassing that you did that in 6 minutes and i've spent 2 days trying to fix my blumber of an attempt.

 

[boblarson]

Thanks, it's almost embarassing that you did that in 6 minutes and i've spent 2 days trying to fix my blumber of an attempt.

Well, don't feel bad. I've been working with Access for almost 14 years now. And the parameters with crosstabs is something I just finally picked up not too long ago.

 

[boblarson]

One of the keys to remember is that you still need to specify the form reference in the crosstab parameters so it will work, even though the form reference is used in the query below it.

 

[chaostheory]

Yea i noticed that, thanks again!