CODEWITHSUNDEEP
sqlvbams-access-2010
atomedic
atomedic

Reputation: 25

Passing Parameter from values to Query

I have built a unbound form with multiple Combo Boxes with the intention of passing the parameters received to an SQL query. My SQL works when all fields contain a value, but when one is null the entire query fails. I have set my where clause for each field to its respective(tblIRS.[Field]=Forms!frmSUPIR![Field] OR Forms!frmSUPIR![Field] IS NULL OR tblIRS.[Field] IS NULL) AND (...

I have also tried to pass the values from the unbound from through variables and then construct the SQL in VBA as well to no avail. Each field has its on line similar to below: 

Dim strFilter As String
Dim strSQL As String 
    If Nz(Me.EMP1, "") <> "" Then
    strFilter = strFilter & "tblIRS.[EMP1] = '" & PQ(Me.txtEMP1) & "' And " ... 
    strSQL = "SELECT * FROM tblIRS " & _
             "WHERE" & strFilter
 End If

The above includes a function to replace PQ.

I have found numerous resources handling the above but none that include how to handle null table values and/or form entries.

My goal is to have the user narrow their search by using as few combo boxes as possible. The form will ideally accept null values in the combo box to be treated as null and not show all records with a null table value for that field.

For example:

I desire to have the query return all instances where EMP1 = Smith, John AND EMP2 = Jones, Mark AND ANY value for EMP3. What is the best way to go about this?

Hope I was clear enough and thank you in advance,

JS

Upvotes: 2

Views: 93

Answers (2)

OpiesDad
OpiesDad

Reputation: 3435

One easy way to accomplish this is to make a different variable for each portion of the where clause:

Dim strWhereEmp1 As String
Dim strWhereEmp2 As String
Dim strWhereEmp3 As String
Dim strFilter As String

If IsNull(Me.EMP1) Then /*Or use If Nz(Me.Emp1, "") = "" if you wish*/
     strWhereEmp1 = "TRUE"
Else
     strWhereEmp1 = "tblIRS.[EMP1] = '" & Me.Emp1 & "'"
End If
If IsNull(Me.EMP2) Then
     strWhereEmp2 = "TRUE"
Else
     strWhereEmp2 = "tblIRS.[EMP2] = '" & Me.Emp2 & "'"
End If
If IsNull(Me.EMP3) Then 
     strWhereEmp3 = "TRUE"
Else
     strWhereEmp3 = "tblIRS.[EMP3] = '" & Me.Emp3 & "'"
End If
strFilter = strWhereEmp1 " AND " & strWhereEmp2 & _
                         " AND " & strWhereEMp3 

strSQL = "SELECT * FROM tblIRS " & _
         "WHERE " & strFilter

You can do this for any number of filters. It will just tack on " AND TRUE" to the where clause if the value from the form is null.

I removed your condition to only create the SQL if Me.Emp1 is not empty, but it can be put back as desired.

Upvotes: 1

mphilipp17
mphilipp17

Reputation: 31

Here's a little console app and method to create your WHERE clause. You don't need to worry about the EMP3 in the resulting where clause because any value is acceptable. 

class Program
{
    static void Main(string[] args)
    {
        var dict = new Dictionary<string, string>()
        {
            { "EMP1", "Smith, John" },
            { "EMP2", "Jones, Mark" },
            { "EMP3", "" }          //This could be null and it would still work

        };

        Console.WriteLine(CreateWhereClause(dict));
        Console.ReadKey();
    }

    public static string CreateWhereClause(Dictionary<string, string> keyValues)
    {
        var result = "WHERE ";

        //Ignore blank values using lamda
        foreach (var kvPair in keyValues.Where(kvp => !String.IsNullOrWhiteSpace(kvp.Value)))
        {
            //If we've already added a clause, add an AND
            if (result.Length > 6)
                result += " AND ";

            result += String.Format(@"tblIRS.[{0}] = '{1}'", kvPair.Key, kvPair.Value);
        }
        return result;
    }
}

Upvotes: 0

Related Questions