Filter Query Using Form...Please Help!

[NickLes518]

I have a form set up to open a query and filter the results based on the selections in my form. However, I want to set it up to filter the results ONLY IF I make a selection in that control box on the form. Currently, if I make a selection in one box and do not select anything in the next, my filtered query will display no results. Is there a way to code this so I can get my filtered results only if a selection is made in that control box on the form?

Thanks

 

[pbaldy]

One way:

http://www.mvps.org/access/queries/qry0001.htm

 

[NickLes518]

Does this work for multiple criteria in the query tho? Basically I want the query to be filtered according to whatever i select in my form, and if nothing is selected I don't want that field to be used in the filter. I attached my database if that makes it easier.

If I select a BuildingType in the LookupForm it asks for a Job parameter and when I don't enter one, it displays all results. Would i just be better off creating 2 different forms and a separate query for each?

Thanks

 

[pbaldy]

Sure, though with more than a couple it can look ungainly. With a number of optional criteria, you can either use Like and a wildcard along with the form reference, or build SQL dynamically like in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

 

[NickLes518]

How do I implement an 'Open Query' function in the SQL then, cuz I want my query to display separate, unlike the subform you used in the sample db?

Private Sub cmdSearch_Click()

DoCmd.OpenQuery(qryDataSheet,[View As AcView = acViewNormal], [DataMode As AcOpenDataMode = acReadOnly])
 
 Dim strSQLHead      As String
    Dim strSQLWhere     As String
    Dim strSQLOrderBy   As String
    Dim strSQL          As String
    Dim strJoin         As String
    
    strJoin = " AND "
    strSQLHead = "SELECT * FROM qryDataSheet "
    
   If Len(Me.cboJobName & vbNullString) Then
        If Len(strSQLWhere) = 0 Then
            strSQLWhere = "WHERE "
        End If
        
        strSQLWhere = strSQLWhere & "[JobName] = " & Me.cboJobName
        
        strSQLWhere = strSQLWhere & strJoin
    End If
       
    If Len(Me.cboBuildingType & vbNullString) Then
        If Len(strSQLWhere) = 0 Then
            strSQLWhere = "WHERE "
        End If
        
        strSQLWhere = strSQLWhere & "[BuildingType] = " & Me.cboBuildingType
        
        strSQLWhere = strSQLWhere & strJoin
    End If
    
    If Len(Me.cboLaborDifficulty & vbNullString) Then
        If Len(strSQLWhere) = 0 Then
            strSQLWhere = "WHERE "
        End If
        
        strSQLWhere = strSQLWhere & "[LaborDifficulty] = " & Me.cboLaborDifficulty
        
        strSQLWhere = strSQLWhere & strJoin
    End If
        
    If Len(Me.cboLaborRate & vbNullString) Then
        If Len(strSQLWhere) = 0 Then
            strSQLWhere = "WHERE "
        End If
        
        strSQLWhere = strSQLWhere & "[LaborRate] = " & Me.cboLaborRate
        
        strSQLWhere = strSQLWhere & strJoin
    End If
      
End Sub

 

[NickLes518]

Nevermind ... after playing around with the query criteria in SQL view I was able to fix it. For some reason it had some half deleted segments of code in it that were throwing things off.

Thanks for the help!

 

[pbaldy]

No problem, glad you got it sorted out.

 

[NickLes518]

Well, I thought I had it figured out. I got it to work using 2 criteria for the query, but when i added criteria to a third field, it wouldn't display any results. Is SQL best option at this point? If so, I'm not to keen on SQL as I am only using Access to help document some projects in my construction line of work. Its kind of frustrating, as this is pretty much the last thing I need to finish. Would you be able to guide me in writing the SQL to use my form to filter my query?

I think I understand the basics behind the sample database you posted earlier, but when I tried to add a command to open the query I got lost.

 

[pbaldy]

Another technique you might try that's fairly easy, if your current criteria in design view looks like this:

Forms!FormName.ControlName

is to change it to:

Like Forms!FormName.ControlName & "*"

which should return all records if the control is empty. Because it uses a wildcard, it can return unexpected results under certain circumstances, which is why I didn't start with it.

 

[NickLes518]

Awesome. That worked much better. I have 2 more small questions:

I would like to be able to filter the query for the building square footage as well. I would make 2 text boxes, one for the lowest value, one for the highest. What should I put in the criteria for that query field in order to keep the results consistent with the rest of my query?

The 2nd question is if I put a date field in the form that just displays the current date, can I filter the query to return results ONLY within 5 years of the current date?


Thanks again for the help, your a life saver!

 

[NickLes518]

I was able to get the date filter to work, but I am struggling with the filter for the SF of the building.

My form is set up as:
'Building SF Between:' [LowGSF] 'And:' [HighGSF]

I want my query to find all records with a GSF value between those 2 quantities I type in on the form. If nothing is entered in the boxes on the form, than I do not want use this filter.

I tried entering criteria:

Between [forms]![frmSearch].[LowGSF] AND [forms]![frmSearch].[HighGSF]

But this made everything in my form detail disappear