Query using multiple parameters

[shall]

I was wondering if anyone can help me I have a search form that users list date range, whether an appointment showed or is pending (Show and pending are seperate checkboxes), and then agent.

On top of that if a field is blank I want it to pull all records with whichever criteria has been selected.

For the form it links to a query and the date range works but I can't seem to get the rest of it so i am trying to add the different criteria. Please help i am still new and am mostly self taught (besides what I have learned in here)

The code below is what I am trying to use on the criteria of the query

[Forms]![Search]![checkShow]=True Or Is Null
[Forms]![Search]![checkPend]=True Or Is Null
[Forms]![Search]![checkRep] = "*"

 

[Brianwarnock]

Picking up where we left off with the other thread.

There are checkboxes bound to the table on which the query is based and you are trying to check their values - yes or no.

If no which is what I understood on the previous thread i pointed you at the way to check the values if the checkboxes are only on the form, to check for a null checkbox value the properties of the checkbox must be set to triple.

Didn't understand the "*"

Brian

 

[shall]

In the database I have a yes/no for the show & pending fields and in the initial form (Entry Form) I have a checkbox. The new search form also has a check box for the same fields along with a combo box for Reps.

In regards to your other post if i was to put that in the column i did tire it and it wouldn't work as it still pulled everything.

Attached is a test database that has what I have been trying to do and some dummy info

Lastly I on the "*" I don't know why I put that there. Probably too tired staring at this screen just threw something in.

 

[Brianwarnock]

Unfortunately I do not have 2007 so cannot open the DB.
However what you need in the design grid is in the say Checkshow col criteria
forms!search!checkshow

This will select when there is a match

If you also want to be able to select all ie whether the selection in the table is True or False then for the checkbox on the Form go into its properties and change Triple State to Yes. This allows for the values True False Null and now add to your criteria
Or forms!search!checkshow Is Null

Brian

 

[shall]

Ok on the search form it is not even letting me narrow it to just shows. I put in the forms!search!checkshow in a column but it still is pulling everything. The date range portion is working fine though.

Attached is screenshot of my query screen since it will not let me save to an earlier version

 

[Brianwarnock]

If you have checkshow set to True it should only select where show is True, however your is Null is done incorrectly, each row in the design grid shows Ands each col shows Ors however it is often necessary to repeat criteria in a Col so you need to repeat your Date check on each row and maybe your checkbox check on the row alongside the opposite checkbox Is Null check. I would avoid all of this by writing in one criteria col

forms!search!checkshow Or forms!search!checkshow Is Null

Brian

Edit took another look at your query and realise I had read it incorrectly, but my comment about how Ors and Ands work still holds. Switch to Sql view and code there it can often be easir to see what is going on and put things together logically.

eg obviously only showing idea
Where (datefield between date1 and date2 or date1 is null or date2 is null) and (show =checkshow or checkshow is null) ....

 

[shall]

It probably would be but i am not at all familiar with SQL.

Now looking over what I need I realized that to have either a check or not it will always show all the info so I changed it up to just have it checked will make it show and i have gotten it to work that way.

The new problem I am having is getting it to show all records check with the rep left blank to show. It will only show if I select a rep and I added a null condition with the rep

Thanks for all your help

also here is the code from the SQL view

SELECT Students.txtFName, Students.txtLName, Students.School.Value, Students.TxtStudentNum, Students.Show, Students.ApptDate, Students.Program.Value, Students.Active, Students.FS, Students.Rep.Value, Students.Reschedule, Students.ReDate, Students.Pending

FROM Students

WHERE (((Students.Show)=[Forms]![Search]![checkShow]) AND ((Students.ApptDate) Between [Forms]![Search]![startDate] And [Forms]![Search]![endDate]) AND ((Students.Rep.Value)=[Forms]![Search]![checkRep] Or (Students.Rep.Value) Is Null) AND ((Students.Pending)=[Forms]![Search]![checkPend])) OR (((Students.Show)=[Forms]![Search]![checkShow]) AND ((Students.Pending)=[Forms]![Search]![checkPend]) AND (([Forms]![Search]![startDate]) Is Null)) OR (((Students.Show)=[Forms]![Search]![checkShow]) AND ((Students.Pending)=[Forms]![Search]![checkPend]) AND (([Forms]![Search]![endDate]) Is Null));

 

[Brianwarnock]

The new problem I am having is getting it to show all records check with the rep left blank to show. It will only show if I select a rep and I added a null condition with the rep

The above makes me wonder if you took on board what I said a few posts back.

If you also want to be able to select all ie whether the selection in the table is True or False then for the checkbox on the Form go into its properties and change Triple State to Yes. This allows for the values True False Null and now add to your criteria
Or forms!search!checkshow Is Null

When a checkbox is Blank its value is No ( False), or if Triple State is No then it could be Null, when you have set the properties to Triple state =Yes then it can appear grey , that is Null.


Brian

 

[shall]

I did use what you had mentioned before about the triple value checkbox and have gotten that part to work now.

But adding a Null to checkRep does not seem to be working any ideas on how to fix that?

 

[Brianwarnock]

I have to confess to not understanding what this is trying to do

((Students.Rep.Value)=[Forms]![Search]![checkRep] Or (Students.Rep.Value) Is Null)

It looks as though you are trying to Select when the Table field matches the Form AND when the table field is null, however unless 2007 is different I don't think that it is possible for the Table field to be Null, atleast I don't know how to define them as Triple State. I thought that it was a typo and that you wanted to do

((Students.Rep.Value)=[Forms]![Search]![checkRep] Or [Forms]![Search]![checkRep] Is Null)

ie select a match or if the checkbox on the form is null select all.


Brian

 

[shall]

That is what I am looking to do but when I replaced the lines (cause it listed that line twice) it still didn't work.

Also it has this funny clitch that the first time I run a report it will not pull anything even if there is info that fits the parameters if I change the parameters and run another report it will pull then i change back to my original setting and it pulls. any ideas on that one?

 

[Brianwarnock]

I feel that without getting my hands on your DB I'm not going to be able to help, so I have put a request for help onto the VIP forum, I hope somebody responds.

Brian

 

[shall]

Well thanks for all your help this is just showing me how much more I need to learn.

 

[boblarson]

My method would be to remove all criteria from the query and make a datasheet form for it. Then use this code to open the filtered form:

Private Sub Command18_Click()
    Dim strWHERE As String
    
    If Len(Me.startDate & vbNullString) > 0 And Len(Me.endDate & vbNullString) > 0 Then
            strWHERE = "[ApptDate] Between #" & Me.startDate & "# AND #" & Me.endDate & "# AND "
        ElseIf Len(Me.startDate & vbNullString) > 0 And Len(Me.endDate & vbNullString) = 0 Then
            strWHERE = "[ApptDate] > = #" & Me.startDate & "# AND "
        ElseIf Len(Me.startDate & vbNullString) = 0 And Len(Me.endDate & vbNullString) > 0 Then
            strWHERE = "[ApptDate] < = #" & Me.endDate & "# AND "
    End If
    
    If Len(Me.checkRep & vbNullString) > 0 Then
        strWHERE = strWHERE & "[Rep.Value] =" & Chr(34) & Me.checkRep & Chr(34) & " AND "
    End If
    
    If Not IsNull(Me.checkPend) Then
        If Me.checkPend = True Then
        strWHERE = strWHERE & "[Pending] = True AND "
    Else
        strWHERE = strWHERE & "[Pending] = False AND "
    End If
    End If
    
    If Not IsNull(Me.checkShow) Then
    If Me.checkShow Then
        strWHERE = strWHERE & "[Show] = True"
    Else
        strWHERE = strWHERE & "[Show] = False"
    End If
    End If
    
    If Right(strWHERE, 5) = " AND " Then
        strWHERE = Left(strWHERE, Len(strWHERE) - 5)
    End If
    
    Debug.Print strWHERE
    
    DoCmd.OpenForm "frmquerystudents", acNormal, , strWHERE
    
            
End Sub

And Brian - the reason they couldn't save the database to 2003 format is that they are using multivalued fields.

 

[shall]

Just to get a little clarification. 1)i am assuming datasheet form is a split form & 2) Does that coding go into the on click event?

 

[boblarson]

Just to get a little clarification. 1)i am assuming datasheet form is a split form

That would be an incorrect assumption. A Datasheet form is one that looks like a table or query when opened but allows you to have events for the form and for the controls which display the data.

& 2) Does that coding go into the on click event?

Does the top part not have sufficient information:

Private Sub Command18_Click()

I used your button on the search form.

 

[shall]

Sorry about that I should have woken up a little better before I replied the previous post make perfect sense. I did set it up and it works except for the null dates, but at this point I am just glad it works and not too concerned about that. As long as I put dates in both field we are good.

 

[boblarson]

This sample should help you get the null dates working.

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[Brianwarnock]

And Brian - the reason they couldn't save the database to 2003 format is that they are using multivalued fields.

I had to Google this, now I understand the rep.value, I was never going to solve this was I?

Glad Bob sorted for you Shall

Brian

 

[shall]

Thanks for all your help