Parameter Form to Input Query Criteria with Multiple Fields Either With a Value or Null

Question

I have built a form with unbound fields designed so a user can input a date range, a facility name (these come from a combobox), and a badge number to generate a query in Access. I want to be able to return results within the selected date range for all facilities if the field is left blank or just the ones for a particular facility if one is selected. I also want to be able to limit the results to those that match an person's badge number.

So the possibilities I want would be:

1. Date Range = defined by user | Facility - All if not selected | Badge # = All if not selected
2. Date Range = defined by user | Facility - All if not selected | Badge # = defined by user
3. Date Range = defined by user | Facility - defined by user | Badge # = All if not selected
4. Date Range = defined by user | Facility - defined by user | Badge # = defined by user

I originally built it with just the date range and facility name and it worked fine. When I try to add in the Badge # it doesn't really work correctly.

My SQL for the WHERE TO section is:

WHERE (((Diversion.Transaction_Date) Between [Forms]![Parameters]![FromDate] And [Forms]![Parameters]![ToDate]) 
AND ((Diversion.Employee_Badge_Number)=[Forms]![Parameters]![BadgeNumber]) 
AND ((Diversion.Facility)=[Forms]![Parameters]![FacilitySelect])) 
OR (((Diversion.Transaction_Date) Between [Forms]![Parameters]![FromDate] And [Forms]![Parameters]![ToDate]) 
AND ((Diversion.Facility)=[Forms]![Parameters]![FacilitySelect]) 
AND ((([Diversion].[Employee_Badge_Number]) Like [Forms]![Parameters]![BadgeNumber]) Is Null)) 
OR (((Diversion.Transaction_Date) Between [Forms]![Parameters]![FromDate] And [Forms]![Parameters]![ToDate]) 
AND ((Diversion.Employee_Badge_Number)=[Forms]![Parameters]![BadgeNumber]) 
AND ((([Diversion].[Facility]) Like [Forms]![Parameters]![FacilitySelect]) Is Null)) 
OR (((Diversion.Transaction_Date) Between [Forms]![Parameters]![FromDate] And [Forms]![Parameters]![ToDate]) 
AND ((([Diversion].[Employee_Badge_Number]) Like [Forms]![Parameters]![BadgeNumber]) Is Null) 
AND ((([Diversion].[Facility]) Like [Forms]![Parameters]![FacilitySelect]) Is Null)) 
OR (((([Diversion].[Facility]) Like [Forms]![Parameters]![FacilitySelect]) Is Null));

To me, it looks like it is including the four possible results that I want to get from the form, but it isn't working right. For instance, if I leave the facility field blank, and define the badge number, it is still giving me all of the results. If I define the facility and define the badge number it does give me the correct results.

Any ideas?

Answer 1

This might give you some ideas building a dynamic query with multiple criteria values. In this example the user can pick any number of the criteria. It is written in VB.Net. It works with Access. I check each field to see if any criteria was provided then append it to the query if there is a valid value. I used Interpolated strings just because it is easier to see where the spaces go. The alternative is:

String.Format("RoasterId = {0} ", itgRoaster)

I also used a String builder which is an efficient way to alter strings without the overhead of creating and disposing of them with each append. You could just use &= if this is not available in VBA.

Dim bolNeedAnd As Boolean = False
        Dim sb As New Text.StringBuilder
        sb.Append("SELECT Coffees.ID, Coffees.[Name], Coffees.RoasterID, Roasters.[Name], Coffees.[Type],Coffees.Rating, Coffees.Comment, Coffees.Description, Coffees.Roast, Coffees.IsExtraBold, Coffees.IsFavorite
    From Coffees Inner Join Roasters on Coffees.RoasterID = Roasters.ID Where ")
        If itgRoaster <> 0 Then
            sb.Append($"RoasterID = {itgRoaster} ")
            bolNeedAnd = True
        End If
        If strRoast <> "" Then
            If bolNeedAnd Then
                sb.Append($"AND Roast = '{strRoast}' ")
            Else
                sb.Append($"Roast = '{strRoast}' ")
            End If
            bolNeedAnd = True
        End If
        If strType <> "" Then
            If bolNeedAnd Then
                sb.Append($"AND Type = '{strType}' ")
            Else
                sb.Append($"Type = '{strType}' ")
            End If
            bolNeedAnd = True
        End If
        If strRating <> "" Then
            If bolNeedAnd Then
                sb.Append($"AND Rating = '{strRating}' ")
            Else
                sb.Append($"Rating = '{strRating}' ")
            End If
            bolNeedAnd = True
        End If
        If bolBold Then
            If bolNeedAnd Then
                sb.Append("AND IsExtraBold = 1 ")
            Else
                sb.Append("IsExtraBold = 1 ")
            End If
            bolNeedAnd = True
        End If
        If bolFavorite Then
            If bolNeedAnd Then
                sb.Append("AND IsFavorite = 1 ")
            Else
                sb.Append("IsFavorite = 1 ")
            End If
        End If
        sb.Append("Order By Coffees.[Name];")
        Debug.Print(sb.ToString)

        Dim cmd As New OleDbCommand With {
            .Connection = cn,
            .CommandType = CommandType.Text,
            .CommandText = sb.ToString}