Missing operator in query expression (1 Viewer)

theferd

Registered User.
Local time
Yesterday, 22:16
Joined
Dec 23, 2019
Messages
29
I'm making use of a form to perform string searches in my database. I don't have much experience in VBA so I initially followed a guide to construct my first module. This worked out well, however when I tried to reapply the same process to any other form it gave me the same Runtime error 3075 : syntax error (missing operator) in query expression...

This one works as intended with no issues.

Code:
Private Sub Searchbutton_Click()
    Dim SQL As String
    
    SQL = "SELECT Project_ID.Project, SAP.SAPNumber, Components.Component_Catalog_Number, Suppliers.Supplier, Assemblies.Assembly " _
       & "FROM Suppliers INNER JOIN (SAP INNER JOIN (Project_ID INNER JOIN (Assemblies INNER JOIN Components ON Assemblies.[Assembly ID] = Components.AssemblyCID) ON Project_ID.Project_ID = Components.ProjectCID) ON SAP.SAPID = Components.SAPCID) ON Suppliers.SupplierID = Components.SupplierCID " _
       & "WHERE [SAPNumber] LIKE '*" & Me.Searchbar & "*' " _

Me.subCsearch.Form.RecordSource = SQL
    Me.subCsearch.Form.Requery

While this one of many others does not

Code:
Private Sub btnManuSearch_Click()
  Dim SQL As String
    
    SQL = "SELECT tblManufacturernum.ManufacturerNum, tblSubctype.ComponentType, tblManufacturername.ManufacturerName" _
        & "FROM tblManufacturername RIGHT JOIN (tblSubctype RIGHT JOIN tblManufacturernum ON tblSubctype.SubctypeID = tblManufacturernum.SubctypeID) ON tblManufacturername.ComponentManufacturerID = tblManufacturernum.ComponentManufacturerID;" _
       & "WHERE [ManufacturerNum] LIKE '*" & Me.txtManuSearchbar & "*' " _
    
    
    Me.manufactsubsearch1.Form.RecordSource = SQL
    Me.manufactsubsearch1.Form.Requery

I'm really at a loss here since the structure of the code is identical and I don't really get what is wrong.
 

isladogs

CID VIP
Local time
Today, 03:16
Joined
Jan 14, 2017
Messages
14,256
Hi
Welcome to AWF. Your post was moderated.
There are a few issues
1. You need to add a space before FROM and WHERE
2. Remove the semicolon before WHERE - it should be at the end
3. Remove the _ at the end of both items of code

Try this
Code:
    SQL = "SELECT tblManufacturernum.ManufacturerNum, tblSubctype.ComponentType, tblManufacturername.ManufacturerName" _
        & " FROM tblManufacturername RIGHT JOIN (tblSubctype RIGHT JOIN tblManufacturernum ON tblSubctype.SubctypeID = tblManufacturernum.SubctypeID) ON tblManufacturername.ComponentManufacturerID = tblManufacturernum.ComponentManufacturerID" _
       & " WHERE [ManufacturerNum] LIKE '*" & Me.txtManuSearchbar & "*';"

If still not correct, add a debug line then paste the immediate window result into the query designer
 

theferd

Registered User.
Local time
Yesterday, 22:16
Joined
Dec 23, 2019
Messages
29
Thanks so much. I don't really get the underlying issue of what the lack of spacing caused, but that seemed to do the trick.
 

MickJav

It wasn't me
Local time
Today, 03:16
Joined
Nov 28, 2005
Messages
2,388
if your entering text in the search field and somebody add a ' I think this will break try using



like """ & "*" & Me![txtManuSearchbar] & "*" & """"


Hope it helps


mick
 

isladogs

CID VIP
Local time
Today, 03:16
Joined
Jan 14, 2017
Messages
14,256
1. The spacing is need to separate each section of the sql.
Otherwise Access would read it as e.g tblManufacturername.ManufacturerNameFROM and throw an error
2. & 3. The semicolon marks the end of the sql statement. Anything after a ; is ignored
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:16
Joined
Feb 28, 2001
Messages
18,740
I don't really get the underlying issue of what the lack of spacing caused

SQL has to parse out the text and uses the space as an item separator. If you run together two text items by not having a space between them then they look like one longer name. When you concatenate parts of a line as you did, either the end of the previous line or the beginning of the current line needs a space as a parsing separator.

EDIT: I see our friend Colin (Isladogs) went there first.
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:16
Joined
Sep 21, 2011
Messages
7,495
Thanks so much. I don't really get the underlying issue of what the lack of spacing caused, but that seemed to do the trick.

If you tried
Code:
Debug.Print SQL

it would be very evident?
 

Users who are viewing this thread

Top Bottom