Help! New to Access - Query Problem

  • Thread starter Thread starter woolly
  • Start date Start date
W

woolly

Hi,

I'm trying to put a database together and so far things are lookin
good. The problem I have is....

I've created a query which filters out records based on selections mad
in a form. It works fine except that I want it to work so that if n
selection is made within a particular box (ie the box is left blank)
it returns all of the records as opposed to none.

I have tried using the "Is Null" code within the field criteria wit
some success. The problem is, when I apply it to more than one field i
stops working.

Basically, I want to be able to filter out records based on a number o
selections, but if one or more box is left blank, I want it to selec
all the records in that field but continue to filter based on the othe
selections.

Tried to explain this as clearly as I can.

Any hints/tips would be greatly appreciated.

Mik
 
Mike:
Try using the Wildcard character * to return any value in a given field or
series of fields:

If Len(MyComboBox )=0 then
strFilterPart1 = "WHERE MyField = *"
Else
strFilterPart1 = "WHERE MyField = '" & MyComboBox & "'"
End If

Create a series of snippets like this for each combo and then put them all
together with the appropriate ANDs and other SQL syntax...
 
Mike:
Try using the Wildcard character * to return any value in a given field or
series of fields:

If Len(MyComboBox )=0 then
strFilterPart1 = "WHERE MyField = *"
Else
strFilterPart1 = "WHERE MyField = '" & MyComboBox & "'"
End If

Create a series of snippets like this for each combo and then put them all
together with the appropriate ANDs and other SQL syntax...
Click to expand...

Note that the = operator does not honor wildcards - the LIKE operator
does. You'll also need quotemarks around the asterisk.

If you want to ignore the combo box, though,it's simpler to just not
reference the field that it searches at all!
 
Back
Top