Search Code Problem

N

nutmeg

I have a search that I would like to have run from three tables.

'strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID,
tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo, tblListNos.MainListNo1,
tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata,
tblHouses.Inactive, tblHouses.FirstOfPostalCode,
tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo,
tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority,
tblListNos.MaintenanceComments, tblListNos.MainIssuedDate " _
'& "FROM (tblHouses LEFT JOIN tblListNos ON tblHouses.RollNumber =
tblListNos.RollNumber) LEFT JOIN tblAccounts ON tblHouses.RollNumber =
tblAccounts.FolioNo " _
'& "ORDER BY tblHouses.RollNumber"

Each time I try to run it I get the following error message.

Syntax error (missing operator) in query expression 'tblHouses.RollNumber
WHERE [tblHouses].[FirstOfHouseAddress] = '12662 112A AVE 1''.

Can anyone tell me what the problem might be.

Thank you,
 
B

Brendan Reynolds

[email protected] said:
I have a search that I would like to have run from three tables.

'strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID,
tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo,
tblListNos.MainListNo1,
tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata,
tblHouses.Inactive, tblHouses.FirstOfPostalCode,
tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo,
tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority,
tblListNos.MaintenanceComments, tblListNos.MainIssuedDate " _
'& "FROM (tblHouses LEFT JOIN tblListNos ON tblHouses.RollNumber =
tblListNos.RollNumber) LEFT JOIN tblAccounts ON tblHouses.RollNumber =
tblAccounts.FolioNo " _
'& "ORDER BY tblHouses.RollNumber"

Each time I try to run it I get the following error message.

Syntax error (missing operator) in query expression 'tblHouses.RollNumber
WHERE [tblHouses].[FirstOfHouseAddress] = '12662 112A AVE 1''.

Can anyone tell me what the problem might be.

Thank you,
Click to expand...


I don't think the code that you have posted is the code that is being
executed, for two reasons, one, it is commented out, and two, there isn't
any "WHERE" anywhere in this code.
 
N

nutmeg

Thank you.

Yes, I had commented it out because it didn't work and just went back to one
table alone, that seemed to work but it didn't work when I added two other
tables. I have added everything back in here for your review. I hope that
this is enough for you to be able to see what I have done wrong. I gather
that I am missing some brackets around something but I don't know what.
Thank you again.

Private Sub CmdSearch_Click()
On Error GoTo Err_CmdSearch_Click

Dim strSQL As String
Dim WhereClause As Boolean

WhereClause = False

strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID,
tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo, tblListNos.MainListNo1,
tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata,
tblHouses.Inactive, tblHouses.FirstOfPostalCode,
tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo,
tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority,
tblListNos.MaintenanceComments, tblListNos.MainIssuedDate" _
& "FROM tblHouses LEFT JOIN tblListNos ON tblHouses.RollNumber =
tblListNos.RollNumber LEFT JOIN tblAccounts ON tblHouses.RollNumber =
tblAccounts.FolioNo" _
& "ORDER BY tblHouses.RollNumber"

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

strSQL = strSQL & " ORDER BY tblHouses.RollNumber "
[Forms]![frm_SearchAddress].RecordSource = strSQL

Exit_CmdSearch_Click:
Exit Sub

Err_CmdSearch_Click:
MsgBox Err.Description
Resume Exit_CmdSearch_Click

'Forms!frm_SearchAddress!btnOpenCalls.SetFocus

End Sub







Brendan Reynolds said:
[email protected] said:
I have a search that I would like to have run from three tables.

'strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID,
tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo,
tblListNos.MainListNo1,
tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata,
tblHouses.Inactive, tblHouses.FirstOfPostalCode,
tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo,
tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority,
tblListNos.MaintenanceComments, tblListNos.MainIssuedDate " _
'& "FROM (tblHouses LEFT JOIN tblListNos ON tblHouses.RollNumber =
tblListNos.RollNumber) LEFT JOIN tblAccounts ON tblHouses.RollNumber =
tblAccounts.FolioNo " _
'& "ORDER BY tblHouses.RollNumber"

Each time I try to run it I get the following error message.

Syntax error (missing operator) in query expression 'tblHouses.RollNumber
WHERE [tblHouses].[FirstOfHouseAddress] = '12662 112A AVE 1''.

Can anyone tell me what the problem might be.

Thank you,
Click to expand...


I don't think the code that you have posted is the code that is being
executed, for two reasons, one, it is commented out, and two, there isn't
any "WHERE" anywhere in this code.
Click to expand...
 
B

Brendan Reynolds

message
OK, well there are at least three problems here.

First the missing parentheses. I can't test this without duplicating your
tables and fields, but I *think* the following should fix the missing
parentheses problem. If it doesn't, the easiest way to resolve this is to
build the basic query using the Access query design grid, then switch to SQL
view. Access will insert the nesesary parentheses for you.

strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID,
tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo,
tblListNos.MainListNo1,tblListNos.MStatus, tblListNos.ProjectID, " & _
"tblHouses.Strata,tblHouses.Inactive,
tblHouses.FirstOfPostalCode,tblHouses.FirstOfHouseNumber,
tblHouses.FirstOfRoadName, tblHouses.LotNo,tblHouses.Telephone1, " & _
"tblHouses.Telephone2,
tblListNos.MPriority,tblListNos.MaintenanceComments,
tblListNos.MainIssuedDate FROM ((tblHouses LEFT JOIN tblListNos ON
tblHouses.RollNumber " & _
"=tblListNos.RollNumber) LEFT JOIN tblAccounts ON tblHouses.RollNumber
=tblAccounts.FolioNo) ORDER BY tblHouses.RollNumber"

The second problem is that the code then goes on to conditionally append a
WHERE clause to the above string, but the string already contains an ORDER
BY clause, and you can't have a WHERE clause after the ORDER BY clause, it
has to come before the ORDER BY clause. And the third problem is that the
code then goes on to append another, identical ORDER BY clause, so that the
string ends up including two ORDER BY clauses. The solution to both of these
problems is the same - remove the first ORDER BY clause.

You might find it helpful to add the following line of code during debuging,
which will output the final string to the Immediate window ...

strSQL = strSQL & " ORDER BY tblHouses.RollNumber "
Debug.Print strSQL '<-- new code here
[Forms]![frm_SearchAddress].RecordSource = strSQL

--
Brendan Reynolds
Thank you.

Yes, I had commented it out because it didn't work and just went back to
one
table alone, that seemed to work but it didn't work when I added two other
tables. I have added everything back in here for your review. I hope
that
this is enough for you to be able to see what I have done wrong. I gather
that I am missing some brackets around something but I don't know what.
Thank you again.

Private Sub CmdSearch_Click()
On Error GoTo Err_CmdSearch_Click

Dim strSQL As String
Dim WhereClause As Boolean

WhereClause = False

strSQL = "SELECT tblHouses.RollNumber, tblHouses.PID,
tblHouses.FirstOfHouseAddress, tblAccounts.AccountNo,
tblListNos.MainListNo1,
tblListNos.MStatus, tblListNos.ProjectID, tblHouses.Strata,
tblHouses.Inactive, tblHouses.FirstOfPostalCode,
tblHouses.FirstOfHouseNumber, tblHouses.FirstOfRoadName, tblHouses.LotNo,
tblHouses.Telephone1, tblHouses.Telephone2, tblListNos.MPriority,
tblListNos.MaintenanceComments, tblListNos.MainIssuedDate" _
& "FROM tblHouses LEFT JOIN tblListNos ON tblHouses.RollNumber =
tblListNos.RollNumber LEFT JOIN tblAccounts ON tblHouses.RollNumber =
tblAccounts.FolioNo" _
& "ORDER BY tblHouses.RollNumber"

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

strSQL = strSQL & " ORDER BY tblHouses.RollNumber "
[Forms]![frm_SearchAddress].RecordSource = strSQL

Exit_CmdSearch_Click:
Exit Sub

Err_CmdSearch_Click:
MsgBox Err.Description
Resume Exit_CmdSearch_Click

'Forms!frm_SearchAddress!btnOpenCalls.SetFocus

End Sub
Click to expand...
<snip>
 
Top