Null acceptance issues

[Ametek]

Hello,

Im creating a database with several search functions which will open queries and filter my data according to several combo box inputs. I would also like it to ignore any boxes left blank however the way I have been doing this is in the query:

[Forms]![FormSearch]![ComboFamily] Or Like [Forms]![FormSearch]![ComboFamily] Is Null

This works fine for up to about 6 searching criteria but gives the error message 'query too complex' once I go over this. Im pretty inexperienced with Access. Is there a simpler work around for this?
Basically what I want to do is have an advanced filter using a form with user input data which will ignore any inputs which are left blank.

Any help would be much appreciated.
Cheers
Am

 

[Dennisk]

When you look at the SQL generated for this type of operation you can see that it gets quite complex. You may have to split the operation into a number of seperate queries then use a union query to append the seperate queries into one.

Another cause of this message is an incorrect reference to a form or control, o check all references to controls as Access will not inform you if a control is not found.

 

[EMP]

See Jon K's basic criteria format and his Notes (1) and (2) in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[Ametek]

RE: Combo box probs

Hi again,

Cheers for the union advice. I have another issue now. I am using combo boxes to select query criteria however at the moment the drop down box shows an option for each record even though many have the same value.
At the moment the combo box uses the following row source:

'SELECT mytable.field FROM mytable'

How would I go about getting it to only display each option once, ie not have the same value repeated over and over on the drop down?

Thanks
Am