How do I alter my VBA script so it shows all the records where a specific field is set to 'Null-CodePudding

I have this split form with some basic search functions based on comboboxes and search fields. Function SearchCriteria is called on the OnChange Events of the comboboxes or by clicking a search-icon on the top of my split-form.

The VBA-code I currently use to filter my record source qry_Administration:

Function SearchCriteria()

Dim Customer, CustomerLocation, CustomerLocationPlace, ExecutionDate, Material As String
Dim Intern, Extern As String
Dim task, strCriteria As String

If Me.chk_AuditEX = True Then
    Extern = "[AuditEX] = " & Me.chk_AuditEX
Else
    Extern = "[AuditEX] like '*'"
End If

 If Me.chk_AuditIN = True Then
    Intern = "[AuditIN] = " & Me.chk_AuditIN
Else
    Intern = "[AuditIN] like '*'"
End If

If IsNull(Me.cbo_CustomerLocations) Then
    CustomerLocation = "[CustomerLocationID] like '*'"
    CustomerLocationPlace = "[LocationCompanyPlace] like '*'"
Else
    CustomerLocation = "[LocationCompanyName] = '" & Me.cbo_CustomerLocations.Column(0) & "'"
    CustomerLocationPlace = "[LocationCompanyPlace] = '" & Me.cbo_CustomerLocations.Column(1) & "'"
End If


If IsNull(Me.cbo_Customers) Then
    Customer = "[CustomerID] like '*'"
Else
    Customer = "[CustomerID] = " & Me.cbo_Customers
End If

If IsNull(Me.txt_ExecutionDateTo) Then
    ExecutionDate = "[ExecutionDate] like '*'"
Else
    If IsNull(Me.txt_ExecutionDateFrom) Then
        ExecutionDate = "[ExecutionDate] like '" & Me.txt_ExecutionDateTo & "'"
    Else
        ExecutionDate = "([ExecutionDate] >= #" & Format(Me.txt_ExecutionDateFrom, "mm/dd/yyyy") & "# And [ExecutionDate] <= #" & Format(Me.txt_ExecutionDateTo, "mm/dd/yyyy") & "#)"
    End If
End If

If IsNull(Me.cbo_Material) Or Me.cbo_Material = "" Then
    Material = "[MaterialID] like '*'"
ElseIf Me.cbo_Material = 6 Then
    Material = "[MaterialID] in (" & TempVars!tempMaterial & ")"
Else
    Material = "([MaterialID] = " & Me.cbo_Material & ")"
End If

strCriteria = Customer & "And" & CustomerLocation & "And" & CustomerLocationPlace & "And" & _
        & ExecutionDate & Material & "And" & Extern & "And" & Intern
            
task = "Select * from qry_Administration where (" & strCriteria & ") order by ExecutionDate DESC"

Debug.Print (task)

Me.Form.RecordSource = task
Me.Form.Requery

After calling SearchCriteria, all the records where for example CustomerLocationID = Null do not show up on my split form. Where do I need to edit my VBA-script so it shows all records? Even if a field is empty. For example: I do not have a CustomerlocationID or I do not have an ExecutionDate yet.

CodePudding user response：

Assuming that what you are asking is "When my search criteria for a field is null, how do I get it to return everything regardless of what's in that field?" Then change your lines like this:

If IsNull(Me.cbo_Customers) Then
    Customer = "[CustomerID] like '*'"
Else
    Customer = "[CustomerID] = " & Me.cbo_Customers
End If

to this:

If IsNull(Me.cbo_Customers) Then
    Customer = " 1=1 "
Else
    Customer = "[CustomerID] = " & Me.cbo_Customers
End If

This causes the WHERE filtering to just ignore what's in that column. There are more complicated ways to do this, but for your code this is the simplest and also fastest.

If on the other hand what you are trying to ask is "When my search criteria for a field is null, how do I return only those rows that have a NULL in that column?" Then change your lines to this:

If IsNull(Me.cbo_Customers) Then
    Customer = "[CustomerID] IS NULL "
Else
    Customer = "[CustomerID] = " & Me.cbo_Customers
End If