Problem With Search VBA Code - Compile Error - Expected: End of Statement (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
Hi all

I've just tried adding a VBA code to a button for searching a subform on one of the databases I am currently building but I am getting the error "Expected: End of Statement "

I have used the below code before many times & haven't had a problem before, here is a sample of the VBA code I use


Code:
Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
    Dim SQL As String

    SQL = "SELECT tblClients.Client, tblClients.[3DigitCode], tblClients.AddressLine1, tblClients.AddressLine2, tblClients.AddressLine3, tblClients.County, tblClients.Postcode " _
    & "FROM tblClients " _
    & "WHERE [Client] LIKE '*" & Me.txtSearchClientList & "*' " _
    & " OR [3DigitCode] LIKE '*" & Me.txtSearchClientList & "*' " _
    & " OR [AddressLine1] LIKE '*" & Me.txtSearchClientList & "*' " _
    & " OR [AddressLine2] LIKE '*" & Me.txtSearchClientList & "*' " _
    & " OR [AddressLine3] LIKE '*" & Me.txtSearchClientList & "*' " _
    & " OR [County] LIKE '*" & Me.txtSearchClientList & "*' " _
    & " OR [Postcode] LIKE '*" & Me.txtSearchClientList & "*' " _
    & "ORDER BY tblClients.[Client] "
    
    Me.SubClientList.Form.RecordSource = SQL
    Me.SubClientList.Form.Requery
    
End Sub

On the database I am building on the list form I have added Criteria on the PlantType field to only display the plant type "NPU" & various criteria for what is shown in the PlantStatus field, I have built the code the same way that I normally do & it looks like the below

Code:
Private Sub btnSearch_Click()
    Dim SQL As String

    SQL = "SELECT tblPlant.PlantListID, tblPlant.PlantType, tblPlant.PlantSubType, tblPlant.PlantID, tblPlant.PlantSerialNo, tblPlant.PlantStatus, tblPlant.DCUNPUID, tblPlant.MotorID, tblPlant.BatteryID " _
    & "FROM tblPlant WHERE (((tblPlant.PlantType)="NPU") AND ((tblPlant.PlantStatus)="Service" Or (tblPlant.PlantStatus)="Active" Or (tblPlant.PlantStatus)="Being Repaired" Or (tblPlant.PlantStatus)="Service Prior To Use" Or (tblPlant.PlantStatus)="Yard Waiting For Service")); " _
    & "WHERE [PlantStatus] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantID] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSerialNo] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantType] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSubType] LIKE '*" & Me.txtKeywords & "*' " _
    & "ORDER BY tblPlant.PlantListID; "
    
    Me.SubNPUListSearch.Form.RecordSource = SQL
    Me.SubNPUListSearch.Form.Requery
    
End Sub

I am getting the error "Expected: End of Statement " in the VBA window with the word "NPU" highlighted, I've read online after clicking on the Help button as to what causes it but I'm not sure how to fix the problem.

Any help would be appreciated

Thanks in advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:28
Joined
Oct 29, 2018
Messages
21,357
Hi Paul. You have two WHERE clauses. You can only have one.
 

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
Hi Paul. You have two WHERE clauses. You can only have one.
Hi theDBguy, thanks for your reply

I was wondering about that & tried changing the 2nd WHERE to an OR like the lines below by still get the error.

Is it not possible to use a search VBA code like I use when there are Criteria specified or is there a way around it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:28
Joined
Oct 29, 2018
Messages
21,357
Hi theDBguy, thanks for your reply

I was wondering about that & tried changing the 2nd WHERE to an OR like the lines below by still get the error.

Is it not possible to use a search VBA code like I use when there are Criteria specified or is there a way around it?
Is this how you tried it?
Code:
Private Sub btnSearch_Click()
    Dim SQL As String

    SQL = "SELECT tblPlant.PlantListID, tblPlant.PlantType, tblPlant.PlantSubType, tblPlant.PlantID, tblPlant.PlantSerialNo, tblPlant.PlantStatus, tblPlant.DCUNPUID, tblPlant.MotorID, tblPlant.BatteryID " _
    & "FROM tblPlant WHERE (((tblPlant.PlantType)='NPU') AND ((tblPlant.PlantStatus)='Service' Or (tblPlant.PlantStatus)='Active' Or (tblPlant.PlantStatus)='Being Repaired' Or (tblPlant.PlantStatus)='Service Prior To Use' Or (tblPlant.PlantStatus)='Yard Waiting For Service')) " _
    & "OR [PlantStatus] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantID] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSerialNo] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantType] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSubType] LIKE '*" & Me.txtKeywords & "*' " _
    & "ORDER BY tblPlant.PlantListID; "
   
    Me.SubNPUListSearch.Form.RecordSource = SQL
    'Me.SubNPUListSearch.Form.Requery
   
End Sub
 
Last edited:

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
Is this how you tried it?
Code:
Private Sub btnSearch_Click()
    Dim SQL As String

    SQL = "SELECT tblPlant.PlantListID, tblPlant.PlantType, tblPlant.PlantSubType, tblPlant.PlantID, tblPlant.PlantSerialNo, tblPlant.PlantStatus, tblPlant.DCUNPUID, tblPlant.MotorID, tblPlant.BatteryID " _
    & "FROM tblPlant WHERE (((tblPlant.PlantType)='NPU') AND ((tblPlant.PlantStatus)='Service' Or (tblPlant.PlantStatus)='Active' Or (tblPlant.PlantStatus)='Being Repaired' Or (tblPlant.PlantStatus)='Service Prior To Use' Or (tblPlant.PlantStatus)='Yard Waiting For Service)) " _
    & "OR [PlantStatus] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantID] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSerialNo] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantType] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSubType] LIKE '*" & Me.txtKeywords & "*' " _
    & "ORDER BY tblPlant.PlantListID; "
   
    Me.SubNPUListSearch.Form.RecordSource = SQL
    'Me.SubNPUListSearch.Form.Requery
   
End Sub
That's exactly how I tried it :rolleyes:
 

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
And you still got the error? Did you copy and paste what I posted and tried it?
Sorry, I thought it was how I had changed it, I tried your version of the code & don't get the error anymore, I get a different error when I try & do a search for the number "4421" like in the attached image

Untitled.png


in the VBA window Me.SubNPUListSearch.Form.RecordSource = SQL is highlighted in yellow
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:28
Joined
Oct 29, 2018
Messages
21,357
Sorry, I thought it was how I had changed it, I tried your version of the code & don't get the error anymore, I get a different error when I try & do a search for the number "4421" like in the attached image

View attachment 81496

in the VBA window Me.SubNPUListSearch.Form.RecordSource = SQL is highlighted in yellow
Is PlantType a number field?

Edit: Oh, I see you're using 4421 to search multiple fields/columns. Are all those columns numeric data type fields?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:28
Joined
May 21, 2018
Messages
8,463
You might want to simplify with an IN

Code:
Private Sub btnSearch_Click()
    Dim SQL As String
    SQL = "SELECT tblPlant.PlantListID, tblPlant.PlantType, tblPlant.PlantSubType, tblPlant.PlantID, tblPlant.PlantSerialNo, tblPlant.PlantStatus, tblPlant.DCUNPUID, tblPlant.MotorID, tblPlant.BatteryID " _
    & "FROM tblPlant WHERE (tblPlant.PlantType = 'NPU') AND (tblPlant.PlantStatus IN ('Service', 'Active','Being Repaired','Service Prior To Use','Yard Waiting For Service')) " _
    & "OR [PlantStatus] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantID] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSerialNo] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantType] LIKE '*" & Me.txtKeywords & "*' " _
    & "OR [PlantSubType] LIKE '*" & Me.txtKeywords & "*' " _
    & "ORDER BY tblPlant.PlantListID; "
    Debug.Print SQL
    'Me.SubNPUListSearch.Form.RecordSource = SQL
    'Me.SubNPUListSearch.Form.Requery
    
End Sub

SQL:
SELECT tblplant.plantlistid,
       tblplant.planttype,
       tblplant.plantsubtype,
       tblplant.plantid,
       tblplant.plantserialno,
       tblplant.plantstatus,
       tblplant.dcunpuid,
       tblplant.motorid,
       tblplant.batteryid
FROM   tblplant
WHERE  ( tblplant.planttype = 'NPU' )
       AND ( tblplant.plantstatus IN (
                   'Service', 'Active', 'Being Repaired', 'Service Prior To Use'
                   ,
                                           'Yard Waiting For Service' ) )
        OR [plantstatus] LIKE '*DOG*'
        OR [plantid] LIKE '*DOG*'
        OR [plantserialno] LIKE '*DOG*'
        OR [planttype] LIKE '*DOG*'
        OR [plantsubtype] LIKE '*DOG*'
ORDER  BY tblplant.plantlistid;
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:28
Joined
May 21, 2018
Messages
8,463
@PaulD2019
When writing SQL in vba. You ALWAYS need a debug.print SQL so that you can error check how your sql resolves. Then take it and drop it into a formatter to see how it looks. I use this
but pick your own. You will save you and others a lot of work.
 

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
Thank you @MajP & @theDBguy

I've saved that link in my favorites

There aren't any errors anymore but the button isn't doing anything at all now
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:28
Joined
May 21, 2018
Messages
8,463
Make sure in all your debugging you remember to remove your commented out code.

Me.SubNPUListSearch.Form.RecordSource = SQL
Me.SubNPUListSearch.Form.Requery
 

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
Make sure in all your debugging you remember to remove your commented out code.

Me.SubNPUListSearch.Form.RecordSource = SQL
Me.SubNPUListSearch.Form.Requery
Do you mean remove both of those lines out of the code?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:28
Joined
May 21, 2018
Messages
8,463
Do you mean remove both of those lines out of the code
Just wondering while you were debugging if you commented these out
I saw that DBGuy commented out a line and you might have copied it.
Me.SubNPUListSearch.Form.RecordSource = SQL
'Me.SubNPUListSearch.Form.Requery

However, most times that requery is not needed anyways. Setting the recordsource should take care of it. But since it did nothing I thought you might have commented both lines out.
For debugging purposes put at the end of your code

Debug.print Me.SubNPUListSearch.Form.RecordSource
Then take your sql string and drop in the query editor to see what gets returned. I did not look at the logic to see if it may return all records.
 

PaulD2019

Registered User.
Local time
Today, 12:28
Joined
Nov 23, 2019
Messages
75
Just wondering while you were debugging if you commented these out
I saw that DBGuy commented out a line and you might have copied it.
Me.SubNPUListSearch.Form.RecordSource = SQL
'Me.SubNPUListSearch.Form.Requery

However, most times that requery is not needed anyways. Setting the recordsource should take care of it. But since it did nothing I thought you might have commented both lines out.
I've tried having them commented out & removing the ' its still not working though, annoying that it isn't working but tbh I could probably do without the search function on the form.

Thanks for both your help in trying to fix the problem :)
 

Users who are viewing this thread

Top Bottom