Invalid field or expression problem

[Shifter]

I'm having issues with Access. I've not used it much before now so don't really know much about it, but i have used SQL and VB before.

I have a union query (which ill call qryA) that references a combo box on a form in one of the where clauses. It also references another query (which i'll call qryB) that also references the same combo box. These both work fine.

However my problem is that I have a third crosstab query (which i'll call qryC) that references the first query (qryA) as it's "table". However when i run this query i get an error saying that my reference to the combobox ([forms]![formname]![comboboxname]) is an invalid field or expression.

If this is confusing i'll try including a diagram type thing:

qryC (transform...select...from qryA...)

qryA
(select...from...where [forms]![formname]![comboboxname]...
union
select...from qryB...)

[PEB]

Is your query A is good And function normally?

[Shifter]

Yes query A, when run by itself, runs perfectly. As does query B. query C (the cross tab query) works perfectly when it references a table that has the same stuff as queryA.

query A was originally used to make the table the query C originally referred to, but i wanted to turn it into a query, instead of a table that constantly needed updating.

Thanks for the quick response by the way :)

[PEB]

Why do not try instaed the paramater value YOU use in query A use the choice done in the combobox?

To do this you have to write an after update statement that deletes the Query A
And creates it using the value of the combo box!

So instaed to have [forms]![formname]![comboboxname]. you'll have "my result"

And when your form isn't open the crosstab can be opened!


And you don't need a separeate table!

[Shifter]

Oh wait! i forgot to mention that queryB also references the combo box. In fact queryB IS the first half of queryA (as in, if you remove the union and the bit after that in queryA, that's queryB).

And i personally don't think that regenerating the query every time the combobox value is changed to hard code the value of the combo box into it is very good. Firstly that defeats the purpose of that part of the query being a dynamic parameter. It also means that whatever parameter is chosen last will carry over still hard-coded into the query for next time the form is open and if the user doesn't change the value in the combo from it's default (which may NOT be the value currently in the query) then the query will still run the values for last time. Of course you could re-write the query when the form is opened up so that the query will go back to the default value, however it doesn't seem like a very good practise to me.

Is there another way to get around this problem still using a dynamic parameter? Maybe if i changed the parameter to being a normal dynamic parameter (as opposed to its current combo-box reference), is there a way to parse parameters through when a query is run, like with a stored procedure?
e.g. queryA (parameter1, parameter2,...)

[Shifter]

I seem to have solved this problem by creating a public function in a module that simply returns the value of the combobox, and using this function in the query instead of directly referencing the combo box itself. Thanks for your help PEB.

[PEB]

Hi,
I'm very happy that your problem is resolved!
About using static expressions in queries... 2004 I used only the dynamic parameters from Forms and this was till my database was small - till 100 000 records!

When my database became bigger My queries runned very slowly! One day I've done a test! I've tried to chaange the dynamic parameter with static expression and the query ran 10 times faster!

Now reelly I don't use paramaters in my queries :)

Have a nice day!