Query Data Using Multiple Criteria from a Form. Some Form Fields = Null

[friedboudinball]

I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?

I have tried setting VBA code as follows:

If Me.Combo1 = Null Then Me.Combo1 = "*"

However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.

Please advise.

 

[BlueIshDan]

You are trying to build form that is able to filter the results of itself?

 

[DavidAtWork]

something appears to be seriously wrong in your logical approach, as BlueishDan stated, you can have a form bound to a query, but the query can't change depending on criteria from it's own data source.
If you have combo boxes on the form, they should be bound to a table or query, but if the forms' underlying record table has no value in it, then it will be blank.
Perhaps you should explain in more detail what you're trying to achieve.

 

[BlueIshDan]

I was simply validating that I knew what friedboudinball's design was.

If he is trying to do what I ask, it is very possible for him to filter the results of his own form by generating a new query using a VBA function that checks the values of each filter control.

 

[friedboudinball]

I'll add more details, hopefully this will help.

My end goal is to generate a query with criteria that come from a "search form."

For example, i want to know all customers in a given location. The user can narrow the results by selecting a State, then a County, then a City, for example. (The locations are pulled from a seperate query but the form itself is not tied to a table). If a user selects all three (State, County, City) the query will run. However, if the County and/or City are left blank the query will not run.

Essentially, i want the query to run regardless of which criteria are available. To get around this, I set up the query with the criteria from the three form fields (State, County, and City). When the entry is made and the query is run, if the other fields are blank (empty) i wanted it to query those as if it was a wild card (*). Thus, i tried:

If Me.Combo1 = Null Then Me.Combo1 = "*"

However, this puts an asterick in the field on the form, but does not translate into an asterisk in the query (it appears). The query pulls up nothing at all. yet if populate each field, it works. My trouble is figuring out how to query multiple fields when each won't always be populated.

HOpefully this helps clear up my question.
thank you for your help.

 

[BlueIshDan]

What are your combo box names and what is your table name and fields. I can do this for you

 

[friedboudinball]

What are your combo box names and what is your table name and fields. I can do this for you

Query SQL:
SELECT tbl_Master_Table.[City], tbl_Master_Table.[State]
FROM tbl_Master_Table
WHERE (((tbl_Master_Table.[City])=[Forms]![frm_Query_Form]![CityCombo]) AND ((tbl_Master_Table.[State])=[Forms]![frm_Query_Form]![StateCombo]));
Table has fields with City and State, among others.

Form has City and State, among others.

In this case, i want to search by State, by City, OR by City and State.

Hope this helps. thanks!

 

[BlueIshDan]

I'm creating you a dynamic function called

Private Function GetSelectQuery(ByVal table_name As String, ByVal select_fields As String, ByVal fields As String) As String

on the event of changing your combo box values set the forms

    Me.RowSource = GetSelectQuery( _
                    "tbl_master_table", _
                    "city, state", _
                    "city " & IIf(IsNull(cityCombo), "LIKE """, " = """) & _
                    IIf(IsNull(cityCombo.Value), "*", cityCombo.Value) & _
                    """" & _
                    ";state " & IIf(IsNull(statecombo), "LIKE """, " = """) & _
                    IIf(IsNull(statecombo.Value), "*", statecombo.Value) & """")

 

[BlueIshDan]

Here we go
Try this out, let me know if you need any modifications.

FUNCTION​

Private Function GetSelectQuery(ByVal table_name As String, ByVal select_fields As String, ByVal fields As String) As String

    Dim sql As String: sql = "SELECT " & select_fields & " FROM " & table_name & " WHERE "
    
    Dim var_table As TableDef
    
    For Each var_table In CurrentDb.TableDefs
    
        If var_table.Name = table_name Then
        
            For Each var_field In Split(fields, ";")
                sql = sql & var_field & " AND "
            Next
            
            sql = Left(sql, Len(sql) - 5)
            
            Exit For
            
        End If
        
    Next

    GetSelectQuery = sql
End Function

COMBO BOX VALUE CHANGE​

    Me.RowSource = GetSelectQuery( _
                    "tbl_master_table", _
                    "city, state", _
                    "city " & IIf(IsNull(CityCombo), "LIKE """, " = """) & _
                    IIf(IsNull(CityCombo.Value), "**", CityCombo.Value) & _
                    """" & _
                    ";state " & IIf(IsNull(StateCombo), "LIKE """, " = """) & _
                    IIf(IsNull(StateCombo.Value), "**", StateCombo.Value) & """")

 

[friedboudinball]

thanks! quite complicated!

 

[BlueIshDan]

thanks! quite complicated!

Sorry, complicated but it can be used in more than one places
Did it work ok for you?

It can also be less complicated.

        sql = GetSelectQuery( _
                    "tbl_example_provinces", _
                    "city, state", _
                    "city = "Saint John";province = "NB")

That would be the most simplified version of it.
Hope you can use this in multiple areas!

Regards,
BlueIshDan

 

[BlueIshDan]

Thank YOU for being the first person to thank me lol

 

[friedboudinball]

i actually used the following ... and seems to work:

in query criteria: like "*" & Form info & "*"

seems to work well.

 

[BlueIshDan]

i actually used the following ... and seems to work:

in query criteria: like "*" & Form info & "*"

seems to work well.

Sorry, what did you do? lol