Query With Lots & Lots of Criteria options

[Wolf]

HI, Im designing a DB for a collage, and need a query with like 20 columns, linked together from 3 tables.

The columns are as follows:

Name, phone, Address, City, State, Zip, SS, DOB, Year, Grade, Tuition, Books, Payments, and so on....

I need a way to select each of the columns by 4 criteria types, Equals to=, Not Equals to, Greater then<, and less then>,


So for eg. the db user can select students who live in a certain zip code, certain year, NOT in grade 5, Born between Sep 1 1990 and Sep 5 1990 with a tuition balance less then $50.

The way I figured ill do it is create a form which has 4 txt boxes for ea of the 20 columns and user can put in whatever criteria he wants, but its very complicated and im running into problems with the text boxes left blank.

The main thing I need is that when no criteria is selected for lets say DOB then he should output all DOB's, and just filter out by the other criteria boxes,

Am I doing it the right way or is there a much more simpler way to do that?

 

[spikepl]

http://allenbrowne.com/ser-62.html

 

[boblarson]

I would use a report (like datasheet if necessary) and then you can open it up with only applicable criteria. This is like how I built one here at work:

Private Sub cmdOK_Click()
    Dim strWhere As String
    Dim strHold As String
    Dim lngCount As Long
    Dim strOrderBy As String
 
    On Error GoTo cmdOK_Click_Error
    strHold = vbNullString
 
    If Me.lstFund.ItemsSelected.Count > 0 Then
        For lngCount = 0 To Me.lstFund.ItemsSelected.Count - 1
            strHold = strHold & Me.lstFund.ItemData(Me.lstFund.ItemsSelected(lngCount)) & ","
        Next
        If Right(strHold, 1) = "," Then
            strHold = Left(strHold, (Len(strHold) - 1))
        End If
        strWhere = "[partner_id] In(" & strHold & ") AND "
        strHold = vbNullString
    End If
 
    If Me.lstMainInternalContact.ItemsSelected.Count > 0 Then
        For lngCount = 0 To Me.lstMainInternalContact.ItemsSelected.Count - 1
            strHold = strHold & Chr(34) & Me.lstMainInternalContact.ItemData(Me.lstMainInternalContact.ItemsSelected(lngCount)) & Chr(34) & ","
        Next
        If Right(strHold, 1) = "," Then
            strHold = Left(strHold, (Len(strHold) - 1))
        End If
        strWhere = strWhere & "[MainContact] In(" & strHold & ") AND "
        strHold = vbNullString
    End If
 
    If Me.lstManagementCompany.ItemsSelected.Count > 0 Then
        For lngCount = 0 To Me.lstManagementCompany.ItemsSelected.Count - 1
            strHold = strHold & Chr(34) & Me.lstManagementCompany.ItemData(Me.lstManagementCompany.ItemsSelected(lngCount)) & Chr(34) & ","
        Next
        If Right(strHold, 1) = "," Then
            strHold = Left(strHold, (Len(strHold) - 1))
        End If
        strWhere = strWhere & "[LegalName] In(" & strHold & ") AND "
        strHold = vbNullString
    End If
 
    If Me.lstGP.ItemsSelected.Count > 0 Then
        For lngCount = 0 To Me.lstGP.ItemsSelected.Count - 1
            strHold = strHold & Chr(34) & Me.lstGP.ItemData(Me.lstGP.ItemsSelected(lngCount)) & Chr(34) & ","
        Next
        If Right(strHold, 1) = "," Then
            strHold = Left(strHold, (Len(strHold) - 1))
        End If
        strWhere = strWhere & "[MGpOrgName] In(" & strHold & ") AND "
        strHold = vbNullString
    End If
 
    If Right(strWhere, 5) = " AND " Then
        strWhere = Left(strWhere, Len(strWhere) - 5)
    End If
 
    If Me.lstSortFieldsSelected.ListCount > 0 Then
        For lngCount = 0 To Me.lstSortFieldsSelected.ListCount - 1
            strOrderBy = strOrderBy & Me.lstSortFieldsSelected.ItemData(lngCount) & ","
        Next
    End If
 
    If Right(strOrderBy, 1) = "," Then
        strOrderBy = Left(strOrderBy, Len(strOrderBy) - 1)
    End If
    strOrderBy = Chr(34) & strOrderBy & Chr(34)
 
    Debug.Print strWhere
    Debug.Print strOrderBy
    DoCmd.OpenReport "rptMissingInsurance", acViewPreview, , strWhere, , strOrderBy
cmdOK_Click_Exit:
    Exit Sub
cmdOK_Click_Error:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdOK_Click of VBA Document Form_frmMissingInsuranceCriteriaAndSort"
    End If
End Sub

 

[Wolf]

Thanks everybody for helping, I used allenbrown method and changed it to just output the filter to the report direct instead of filtering it on the form, so the form basically is unbound.


This is what I did.

Created all the textboxes for each field I added 4 text boxes,
and then the click command for the runqry button,


Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.


If Not IsNull(Me.Eql1) Then
strWhere = strWhere & "([Name] = """ & Me.Eql1 & """) AND "
End If

If Not IsNull(Me.NtEql1) Then
strWhere = strWhere & "([Name] <> """ & Me.NtEql1 & """) AND "
End If

If Not IsNull(Me.GrtrTn1) Then
strWhere = strWhere & "([Name] >= """ & Me.GrtrTn1 & """) AND "
End If

If Not IsNull(Me.LsTn1) Then
strWhere = strWhere & "([Name] < """ & Me.LsTn1 & """) AND "
End If



If Not IsNull(Me.Eql7) Then
strWhere = strWhere & "([DOB] = " & Format(Me.Eql7, conJetDate) & ") AND "
End If

If Not IsNull(Me.NtEql7) Then
strWhere = strWhere & "([DOB] <> " & Format(Me.NtEql7, conJetDate) & ") AND "
End If

If Not IsNull(Me.GrtrTn7) Then
strWhere = strWhere & "([DOB] >= " & Format(Me.GrtrTn7, conJetDate) & ") AND "
End If

If Not IsNull(Me.LsTn7) Then
strWhere = strWhere & "([DOB] < " & Format(Me.LsTn7, conJetDate) & ") AND "
End If



If Not IsNull(Me.Eql8) Then
strWhere = strWhere & "([Age] = " & Me.Eql8 & ") AND "
End If

If Not IsNull(Me.NtEql8) Then
strWhere = strWhere & "([Age] <> " & Me.NtEql8 & ") AND "
End If

If Not IsNull(Me.GrtrTn8) Then
strWhere = strWhere & "([Age] >= " & Me.GrtrTn8 & ") AND "
End If

If Not IsNull(Me.LsTn8) Then
strWhere = strWhere & "([Age] < " & Me.LsTn8 & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
End If


DoCmd.OpenReport "QryMn", acViewPreview, , strWhere

End Sub



BTW The list of filters goes on I just shortened it to show you what I did.

 

[Wolf]

I just ran into a vba runtime error 3464
When I tried to filter by a field that relays to a currency field,
I thought currency gets treated like a numeric field.
What part of my code do I have to change to make it work in currency fields?

Here is the code with the problem,

If Not IsNull(Me.Text161) Then
strWhere = strWhere & "([Budget] = " & Me.Text161 & ") AND "
End If

If Not IsNull(Me.Text162) Then
strWhere = strWhere & "([Budget] <> " & Me.Text162 & ") AND "
End If

If Not IsNull(Me.Text163) Then
strWhere = strWhere & "([Budget] >= " & Me.Text163 & ") AND "
End If
If Not IsNull(Me.Text164) Then
strWhere = strWhere & "([Budget] < " & Me.Text164 & ") AND "
End If

Budget is the currency field on my report and text164 is the unbound strwhere form field.

Thanks in advance

 

[boblarson]

Currency is the same as numbers. But I think you may have a problem with your code like this:

If Not IsNull(Me.Text161) Then

you should use

If Len(Me.Text161 & vbNullstring) > 0 Then
' filter stuff here
End If

that deals with both nulls and empty strings. Also, you should rename your controls to meaningful names now before you get too far in so that people who come after you (or you, a year down the line) can look and know that txtOrderID is the OrderID instead of having to go track down Text174 and figure out what it is.

Use meaningful names for all of your objects. It will save you, and others who may follow after you, lots of time and effort (this comes from me having to deal with 3 large databases that someone built and they didn't name their controls, queries, tables, etc. with meaningful names. It was a major pain in the backside to deal with).

 

[Wolf]

you should use

If Len(Me.Text161 & vbNullstring) > 0 Then
' filter stuff here
End If

Thank you that did it.
Should I use it for all other text fields as well or only for numeric?

BTW thanks for the tip on renaming the boxes, you have a good point.

 

[boblarson]

Should I use it for all other text fields as well or only for numeric?

I use it for all types.

 

[Wolf]

you should use

If Len(Me.Text161 & vbNullstring) > 0 Then
' filter stuff here
End If

For some reason I still have a problem with this error on some of my fields (at lease half of them are solved though)
By calculated fields it works fine, but by fields that take the currency value straight off my table I get the error if I try to put in as a filter