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
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
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
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