Problem with Syntax Error (Missing Operator)

[Guest]

Hello,
I have a form for searching for addresses. It runs fine when it only runs
with data from only 1 table, the minute I add a second table I get an error
message "Syntax error (missing operator) in query expression
'tblHouses.IDNo=tblOwners.IDNo WHERE tblHouses.HouseAddress ='...( "the
addess I'm searching for"). I can't identify what the problem is that is
causing the error.

Form runs on Record Source:
SELECT tblHouses.IDNo, tblHouses.HouseAddress, tblOwners.OwnerName FROM
tblHouses INNER JOIN tblOwners ON tblHouses.IDNo = tblOwners.IDNo;

OnLoad

Dim strSQL As String

strSQL = "SELECT tblHouses.IDNo, tblHouses.HouseAddress,
tblOwners.OwnerName FROM tblHouses INNER JOIN tblOwners ON tblHouses.IDNo =
tblOwners.IDNo"
Form_frm_SearchAddressTest1.RecordSource = strSQL

There is an Unbound search box on the form called HouseSearchAddress using:
SELECT tblHouses.HouseAddress FROM tblHouses ORDER BY
tblHouses.RoadnameCivic;

OnCmdSearch

Dim strSQL As String
Dim WhereClause As Boolean

WhereClause = False

strSQL = "SELECT tblHouses.IDNo, tblHouses.HouseAddress, tblOwners.OwnerName
FROM tblHouses INNER JOIN tblOwners ON tblHouses.IDNo = tblOwners.IDNo"

If SearchHouseAddress.Value <> "" Then
strSQL = strSQL & "WHERE tblHouses.HouseAddress = '" &
SearchHouseAddress.Value & "' "
End If

strSQL = strSQL & "ORDER BY IDNo "
Form_frm_SearchAddressTest1.RecordSource = strS

Any help gratefully accepted.
IEJ

 

[John Spencer]

You seem to be missing spaces when you add the where and Order by clauses to
STRSQL. Try putting a space before WHERE and one before ORDER
strSQL = strSQL & " WHERE tblHouses.HouseAddress = '" &
SearchHouseAddress.Value & "' "

strSQL & " ORDER BY IDNo "

Also the test should be
IF Len(SearchHouseAddress.Value & vbNullstring) > 0 then

END IF

Or use
IF NZ(SearchHouseAddress.Value,"") <> "" Then

 

[Guest]

Thank you John for the help but unfortunately the spaces didn't help I still
got an error message. Any other thoughts. Also, where should I be putting
the test code you suggested.

Thanks,
IEJ