problem with criteria in query

[Hey]

I'm trying to run an append query and I'm having trouble. If I hardwire the
criteria it works fine. But as soon as I try to use a separate form I made
requesting invoice numbers from and to it bombs out with "The Microsoft Jet
database engine does not recognize "[Forms]![frmAnything]![AnyNumber]" as a
valid field name or expression."

Example: query listing ALL invoices works fine. Limit query to invoice
number 14533 will work fine as long as I put 14533 into the criteria. If I
leave that off of criteria, but input [Forms]![frmAnything]![Anynumber] into
criteria it gives me the error message. Whether I have that frmAnything
open or not.

On older queries, even if I don't have the source form open for the
criteria, it requests the specific controls when I try to run it and then
runs the query. It won't on this new one? It gives me the above error no
matter how I try to use the numbers (> first number, "between" the numbers,
etc). Yet, same numbers manually input into the criteria work fine.

Even if I switch it to another pre-existing form that asks for say client
number instead of invoice number (that works on other queries fine)...it
gives me this type of error.

I tried running same database under Access2003 and same problem.

Has Access (or, Jet) gotten screwed up? The database?

Help!

 

[John W. Vinson]

I'm trying to run an append query and I'm having trouble. If I hardwire the
criteria it works fine. But as soon as I try to use a separate form I made
requesting invoice numbers from and to it bombs out with "The Microsoft Jet
database engine does not recognize "[Forms]![frmAnything]![AnyNumber]" as a
valid field name or expression."

That's odd. Normally you'ld just get a prompt for the Forms! etc.

Please post your SQL. Any Paramters set? Is this a local table or a linked
one? If linked, where is it linked from?

 

[John W. Vinson]

Already run through this possibility. But even if I've spelled something
wrong, it doesn't give me this type of error on other queries. It asks me
at run time to define (input) the fields I've used as the criteria. On this
particular query (and others that are similar) it gives me this error.
Trust me, I've MADE SURE the spelling is correct (I've even cut and pasted
to be sure).

The form is indeed open - in its own right and not as a subform, at the time
you run the query? (sorry, maybe too obvious, but I've made that mistake!)

 

[Hey]

Yes, separate form, not a subform.

Okay, the latest:

I went through elimating all the tables and queries I'm using in the query
where I can use embedded limits, but not from my separate filter form. If I
eliminate the crosstab query that's a part of this query, it will take the
controls of the filter form and use them.

So, all this seems to be coming from a crosstab query that's part of this
append query (just to limit appending of only those statements that have a
balance on them--so, not a zero totaled statement).

I guess it's impossible to limit a crosstab query in this way? Again, if I
embed the parameters in the criteria it works fine, even just in the
crosstab query itself, but I can't get it to use parameters from my
forms!frmAnything.Anynumber.

I've tried doing that just in the crosstab. No go. I've tried making part
of the crosstab a query that's limited by this forms!frmAnything.Anynumber
and no go.

So, it's not possible to limit a crosstab query in this way? Is there a
work around?

Thanks again