Now I have the combo box returning the string but the 2 codes in red font still gives an error as in the attachment. My immediate window has this result now........ SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO WHERE HOUSE_NO=211 AND dbo_NUCSTREET.STREET_NO=6206 AND UNIT_NO= AND HOUSE_NO_SUFFIX=
I don't want to be checking for unit_no and suffix if it is null and if the address does not contain that at all. Only check and search if it is in address. But error comes up
Code:
Private Sub SearchButton_Click()
If OptionGroup.Value = 1 Then
Dim strSQL As String
If Not IsNull(Me.HouseNo) Then
strSQL = "HOUSE_NO=" & Me.HouseNo
End If
If Not IsNull(Me.StreetID) Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "dbo_NUCSTREET.STREET_NO=" & Me.StreetID
End If
If Not IsNull(Me.UnitNo) Then
strSQL = strSQL & " AND " & "UNIT_NO=" & Me.UnitNo
End If
If Not IsNull(Me.Suffix) Then
strSQL = strSQL & " AND " & "HOUSE_NO_SUFFIX=" & Me.Suffix
End If
strSQL = "SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME " _
& "FROM dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO " _
& " WHERE " & strSQL
Debug.Print strSQL
Me.Property_Information.Form.RecordSource = strSQL
End If
End Sub