Parameter Query from Form

[Guest]

Hello,
I have a query that pulls 9 parameters from a form. When I first made the
database, it only had 5 parameters. I set up each field in my query with the
appropriate criteria ([forms]![frmMaster]![FieldName] OR
IsNull([forms]![frmMaster]![FieldName]). After I saved and reopened my
query, it had propogated itself with every possible combination of the
parameter criteria. After I added the other 4 fields and their parameter
criteria, however, it did not repropogate to accomodate the new possible
combinations. Now, is there an easy way to get Access to propogate itself,
or am I going to have to enter all of the remaining 480 possble combinations
myself?
Thanks!
Melinda

 

[John Spencer (MVP)]

I think you have a problem. With that many criteria, it is highly likely that
you will get a query is too complex message.

It is time to start using VBA to build the where criteria of the query. OR you
can try one of several other ways to solve the problem.

One method for text fields is:

Field: NZ(YourFieldA,"")
Criteria: Like NZ(Forms!frmMaster!FieldName,"*")

You can actually use variations of that technique for number and date fields.
The drawback to the technique is that it will be slow since the query won't be
able to use any indexes that exist on the specified fields.

By the way, you can get Access to reformat (or at least attempt to reformat) by
deleting all the criteria (and the extra calculated fields) and re-entering the
criteria as you originally did.

 

[Guest]

Thanks for the input John. I'm not much of a VBA programmer, so I switched
to using Like in my criteria, which does exactly what I wanted. And my query
was already slow, so this isn't any worse. Also, I tried re-entering all of
the criteria and it didn't work. Oh, well.
Melinda