Query With a Form

[b28zu01]

I am having trouble using a form to define parameters in a query. I would like to have the query look at the form and query by that. First I want to make a selection in two check boxes, I have the fields set up as yes/no and I need to query based on both. Right now I can get the query to run if the boxes are check in the form but if they are not it will not give me any data. Here is the expression I am using.
[Forms]![form name]![field name].

 

[Jon K]

Try setting the Default Value property of the two check boxes on the form to False.
.

 

[b28zu01]

Ok thanks that works great. I am trying to do the same thing with a text box in the form. As long as I have data in the form it works but if there is no data in the form I want it to bring all items. Is there someting I can set its default value to, and make this work?

 

[Steve R.]

You can use the "ISNULL" function, so if ISNULL is true, all records are pulled up. With "ELSE" being used to pull up the filtered records.

A potential concern is the fact that NULL and an empty string are two different values, so you may want to test for both cases.

If IsNull(textbox) OR textbox ="" then
display all results
Else
display filtered data in textbox
End if

 

[b28zu01]

Does that go in the default value of the field or in the query? There are no Null values in the field. I want the query to display all values if the form is null, or display values only by what is typed in the form.

 

[Steve R.]

No. The textbox is on the form. The on the query pane, you would have a reference to that text box. So that if the value of the textbox is null or a zero length string the query gets a "*" otherwise it gets your "MyString".

 

[b28zu01]

I could not get that to work. I think what I need is something like this just in expression.

Query speaking here.
If the field named (x) is null then pull all values, if the field has data in it then pull that value.

Or

On the form if there is some way to put the default value to wildcard or all.

 

[Steve R.]

Below is an abbreviated code version. My main form has 6 combo boxes. The combobox names are in the form "Cnum1". If an item is not SELECTED then all the items for that field are shown. If one item is selected then the filter is set to show only that one item. In the sample of Case 2 only a specific agency would be shown. Case 1 shows all values for all records, note that the FilterON property is set to FALSE so all records are shown this is equivalent to "Select *"

    Rem NO Selection Made
    If Cnum1 = 24 And Cnum2 = 32 And Cnum3 = 7 And Cnum4 = 9 And Cstr5 = "No Selection" And Cstr6 = "No Selection" Then Case1num = 1
    REM 1 item selected, in this case Cnum1
    If Cnum1 <> 24 And Cnum2 = 32 And Cnum3 = 7 And Cnum4 = 9 And Cstr5 = "No Selection" And Cstr6 = "No Selection" Then Case1num = 2
...
        Case 1 'NO items selected show all records
            Case2num = 1
        Case 2 '1 Item Selected
            FilterNamestr = "[agency] = " & Cnum1

....
    Select Case Case2num 'Establish Filter
        Case 1 'No Selection Made.  Display all records.
            Me!TYPE5SUBFRM.Form.FilterOn = False
            Me.Text17.Value = DCount("*", "projectnumqry")
            FilterNamestr = ""
        Case 2 'Selection Made.  Display Records through filter.
            Me!TYPE5SUBFRM.Form.Filter = FilterNamestr
            Me!TYPE5SUBFRM.Form.FilterOn = True
            Me.Text17.Value = DCount("*", "type5frmqry", FilterNamestr)
        Case Else 'No Matching Case Number
            MSG1 = "No matching case statement found.  Check the code in Case2num. Case2num = " & Case2num
            TITLE1 = "Select Statement Error"
            MsgBox MSG1, vbOKOnly, TITLE1
    End Select

The results of the FILTER (query) are shown in a subform that is in datasheet view below the six comboboxes on the main form. The code for limiting the results in the subform is:

Me!TYPE5SUBFRM.Form.Filter = FilterNamestr

 

[Jon K]

Query speaking here.
If the field named (x) is null then pull all values, if the field has data in it then pull that value.

The attached sample database shows how to do it in a query.

Notice that the Triple State property of the two check boxes on the form has been set to Yes so that the check boxes can be Null (grayed), Checked or Unchecked. (However, in order to visually display the grayed option, I have to set their Special Effect property to Raised, too. Not sure if it is caused by my defective monitor or a bug in my version of Access 2003.)

The criteria used are based on the basic criteria format in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312
.