Form criteria returns only non-null results

[chris-uk-lad]

Hi all.

I am using a form that checks a table for results based on the cirteria entered in the textboxs provided. This currently works fine for a table i have where all 4 columns are populated. Im now using a table that has many null values, yet running the same code only returns values where there are no nulls, and not all like i wish.

e.g if i click the search button at the start of the tool (with empty textboxs should return all records) it only returns 3 records (as these have all 4 columns populated in the table) and doesnt show the remaining ones as Forename and Reference are Null for these. I think the problem resides in the SQL im running to the table shown below.

    If IsNull(Surname) Then
        strSurname = "SURNAME LIKE '*'"
    Else
        strSurname = "SURNAME LIKE '" & Surname & "*'"
    End If
 
    If IsNull(FORENAME) Then
        strForename = "FORENAME LIKE '*'"
    Else
        strForename = "FORENAME LIKE '" & FORENAME & "*'"
    End If
 
    If IsNull(DATE) Then
        strDate = "Date LIKE '*'"
    Else
        strDate = "Date ='" & Format(DATE, "YYYY-MM-DD") & "'"
    End If
 
    If IsNull(Ref) Then
        strOtherRef = "Ref LIKE '*'"
    Else
        strOtherRef = "Ref = '" & Ref & "'"
    End If
    strWhere = "WHERE " & strSurname & " AND " & strForename & " AND " & strDate & " AND " & strRef
    strSQL = "SELECT * FROM tblMember " & strWhere & " ORDER BY SURNAME, FORENAME"

You will likely question the strSQL line using strWhere within, was for ease.

All help appreciated, thanks

 

[DCrake]

You are nearly right, however my apporach would be as follows

Dim strCriteria As String


If Not IsNull(Me.Surname) Then
   strCriteria = " Surname Like '"  & Me.Surname & "*' AND "
End If

If Not IsNull(Me.Forename) Then
  strCriteria = strCriteria & " Forename Like '" & Me.Forename & "*' AND "
End If

If Not IsNull(Me.Date) Then
  strCriteria = strCriteria & "Date =#" & Format(Me.Date,"YYYY-MM-DD") & "# AND "

If Not Is Null(Me.Ref) Then
  strCriteria = strCriteria & "Ref Like '" & Me.Ref & "*' "
End If

'Test for and remove trailing AND from string

If Len(strCriteria) > 0 Then
    If Right(strCriteria,4) = "AND " Then
         "WHERE " & strCriteria = Left(strCriteria,Len(strCriteria)-4)
    End If
End If

Debug.Print strCriteria 

strSQL = "SELECT * From tblMember " & strCriteria & " ORDER BY Surname, Forename;"

Debug.Print strCriteria

In the above you are only creating conditions if the user has entered a value in the search fields on your form.

This is air code and needs testing.


BTY Do not use field names such as DATE as this is a reserved word.
David

 

[chris-uk-lad]

Hi, thanks for the response, it works fine for clicking search without entering criteria, though, when i typed in a surname to search for, it returned a syntax error in the from clause, apparently its with strSQL containing:

SELECT * From tblMember  Surname Like 'Johns*' AND  ORDER BY Surname, Forename;

Which shows 'WHERE' is missing, originating from the following returning 'False'

If Right(strCriteria, 4) = "AND " Then
         strWhere = "WHERE " & strCriteria = Left(strCriteria, Len(strCriteria) - 4) & ""
End If

I include the slightly ammended version of the solution u provided (couple syntax issues)

    Dim strCriteria As String
 
    If Not IsNull(Me.Surname) Then
        strCriteria = " Surname Like '" & Me.Surname & "*' AND "
    End If
 
    If Not IsNull(Me.FORENAME) Then
        strCriteria = strCriteria & " Forename Like '" & Me.FORENAME & "*' AND "
    End If
 
    If Not IsNull(Me.DATE) Then
        strCriteria = strCriteria & "Date =#" & Format(Me.DATE, "YYYY-MM-DD") & "# AND "
    End If
 
    If Not IsNull(Me.Ref) Then
        strCriteria = strCriteria & "Ref Like '" & Me.Ref & "*' "
    End If
 
    'Test for and remove trailing AND from string
    If Len(strCriteria) > 0 Then
        If Right(strCriteria, 4) = "AND " Then
            strWhere = "WHERE " & strCriteria = Left(strCriteria, Len(strCriteria) - 4) & ""
        End If
    End If
    strSQL = "SELECT * From tblMember " & strCriteria & " ORDER BY Surname, Forename;"
 
    Form_frmSearchResults.SetSearchResults strSQL
    Form_frmSearchResults.Refresh

 

[DCrake]

Have you heard the phrase "aircode" this refers to any code that the provider has sent you which has been typed in as they are thinking. Normally this has not been tested and usually does not contain the table and field names that the recepient uses. Time to time errors occur, and identifying them and correcting them helps you understand the problem you were encountering in the first instance. Another term you may come across, especially on invoices is "EOE" Errors or Omissions Excepted. In other words if there is a problem with the invoice them the creator is not leagally bound by it, in other words it's a get out clause.

David

 

[chris-uk-lad]

Hi DCrake, yes i was aware of Aircode and did realise that it was theoretical and that i would need to adapt it to working as you did on the quick without testing and was very much appreciative of the fact that you did this.

I have identified that the problem is with AND placed after each piece of criteria, so when only 1 textbox contains values to search for, the SQL syntax is wrong. Im just trying to find a way around this.

 

[DCrake]

did you notice the bit of code

If Len(strCriteria) > 0 Then
    If Right(strCriteria,4) = "AND " Then
         "WHERE " & strCriteria = Left(strCriteria,Len(strCriteria)-4)
    End If
End If

Whilst adding the word AND to the end of each control test it is working on the worst case senerio that all controls will be populated, however, if one or more are not then the word AND would be the trailing word. So the abouve code simply checks for the last word not being an AND if so it drops the word from the string.

Test 1

No criteria added in any of the fields

strCriteria = ""

If Len(strCriteria) > 0 Then
No where criteria to apply to sql statement
End If

Test 2

Only field one has criteria in it

strCriteria = "[FieldName]='" & Me.TextBox1 & "' AND "


Test 3

Fields 1 and 3 have criterion in them

strCriteria = "[FieldName1]='" & Me.TextBox1 & "' AND "

strCriteria = strCriteria & [FieldName3]='" Me.TextBox3 & "' AND "

Test 4

Only field 4 has criteria in it

strCriteria = "[FieldName4]='" & Me.TextBox4 & "'"


Test last word in strCriteria

If Right(strCriteria,4) = "AND " Then
Add the prefix WHERE and drop the final AND
See above.
End If

David