Repost-Assist with Syntax Error(Missing Op)

[nutmeg]

Would anyone be able to assist with this problem. John had suggested adding
spaces in front of WHERE and ORDER by but that didn't work. I'm desparate to
get this form to run.
Thank you,
I

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

 

[Brian Bastl]

Nutmeg,

comments and confusion in-line:

Would anyone be able to assist with this problem. John had suggested adding
spaces in front of WHERE and ORDER by but that didn't work. I'm desparate to
get this form to run.
Thank you,

If you've copy and pasted your code into this post, then John is absolutely
correct.

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

Are you using the Load event to set the record source for your loading form,
or for another form?

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

You say that the search box (above) is named HouseSearchAddress, but you're
calling it SearchHouseAddress in the WHERE clause below. What gives? Did you
actually copy and paste this stuff? If not, then how is anyone to help you
without knowing what you've actually tried to code? Perhaps you could
include the entire event procedure.

OnCmdSearch

Dim strSQL As String
Dim WhereClause As Boolean

WhereClause = False

What is the dimmed WhereClause for?

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

Assuming you copy and pasted this code, then you are in fact missing a space
between the leading quotation mark and WHERE (above), and between the
leading quotation mark and ORDER BY (below), just as John noted.

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

=strS? It should read =strSQL

Is the above form name correct? Form_frm_SearchAddressTest1?
Or is it frm_SearchAddressTest1?
The correct syntax would be Forms![YourFormName].RecordSource = strSQL
or Forms("YourFormName").RecordSource = strSQL

There may be other issues.

HTH,
Brian