Building a WHERE IN SELECT Subquery in Access VBA through concatenation

Question

I'm attempting to build an inventory sorting and tracking system in Access 2016 for people that are minimally computer literate, and I've been asked to narrow it down to one search bar rather than separate filters. After quite a bit of poking around, I've managed to get one working, when I went to replicate the design for another department, it's giving me a syntax error regarding the SQL statement my code is building. The format for the one that works is exactly the same, and returns the same error when run through an online validator, but works functionally in practice.

The user enters text into textbox MainSearch, which builds the search string (showing here as 1155 and so on), and filters the form results on the AfterUpdate event. Pretty sure I can manage debugging before and after, but I can't, for all the research I'm doing, figure out where my error with the SQL syntax is. I included the other function that it uses just in case it's relevant to someone that knows more than me.

Function ExecuteSearch(strCriteria As String) As Boolean
On Error Goto Err_ExecuteSearch

Dim strSQL As String
Dim strSQLWhere As String
Dim lngCount As Long
Dim rst as Recordset

strCriteria = "*" & Me.MainSearch.Text & "*"
Trim (strCriteria)
strCriteria = Replace(strCriteria, "'", "")

strSQL = "SELECT * FROM REOEM"

strSQLWhere = "ID IN (SELECT ID FROM REOEM WHERE Manufacturer LIKE 'strCriteria' OR Type LIKE 'strCriteria' OR Make LIKE 'strCriteria' OR Lester LIKE 'strCriteria' " _
& "OR OEM1 LIKE 'strCriteria' OR OEM2 LIKE 'strCriteria' OR OEM3 LIKE 'strCriteria' OR OEM4 LIKE 'strCriteria' OR OEM5 LIKE 'strCriteria' OR OEM6 LIKE 'strCriteria' OR OEM7 LIKE 'strCriteria' OR OEM8 Like 'strCriteria' OR OEM9 Like 'strCriteria' OR OEM10 LIKE 'strCriteria' " _    
& "OR OEM11 LIKE 'strCriteria' OR OEM12 LIKE 'strCriteria' OR OEM13 LIKE 'strCriteria' OR OEM14 LIKE 'strCriteria' OR OEM15 LIKE 'strCriteria' OR OEM16 LIKE 'strCriteria' OR OEM17 LIKE 'strCriteria' OR OEM18 LIKE 'strCriteria' OR OEM19 LIKE 'strCriteria' OR OEM20 LIKE 'strCriteria' " _
& "OR OEM21 LIKE 'strCriteria' OR OEM22 LIKE 'strCriteria' OR OEM23 Like 'strCriteria' OR OEM24 LIKE 'strCriteria' OR OEM25 LIKE 'strCriteria' OR OEM26 LIKE 'strCriteria' OR OEM27 LIKE 'strCriteria' OR OEM28 LIKE 'strCriteria' OR OEM29 LIKE 'strCriteria' OR OEM30 LIKE 'strCriteria' " _
& "OR OEM31 LIKE 'strCriteria' OR OEM32 LIKE 'strCriteria' OR OEM33 LIKE 'strCriteria' OR OEM34 LIKE 'strCriteria' OR OEM35 LIKE 'strCriteria' OR OEM36 LIKE 'strCriteria' OR OEM37 LIKE 'strCriteria' OR OEM38 LIKE 'strCriteria' OR OEM39 LIKE 'strCriteria' OR OEM40 LIKE 'strCriteria' " _
& "OR OEM41 LIKE 'strCriteria' OR OEM42 LIKE 'strCriteria' OR OEM43 LIKE 'strCriteria' OR OEM44 LIKE 'strCriteria' OR OEM45 LIKE 'strCriteria' OR OEM46 LIKE 'strCriteria' OR OEM47 LIKE 'strCriteria' OR OEM48 LIKE 'strCriteria' OR OEM49 LIKE 'strCriteria' OR OEM50 LIKE 'strCriteria' " _
& "OR OEM51 LIKE 'strCriteria' OR OEM52 LIKE 'strCriteria' OR OEM53 LIKE 'strCriteria' OR OEM54 LIKE 'strCriteria' OR OEM55 LIKE 'strCriteria' OR OEM56 LIKE 'strCriteria' OR OEM57 LIKE 'strCriteria' OR OEM58 LIKE 'strCriteria' OR OEM59 LIKE 'strCriteria' OR OEM60 LIKE 'strCriteria' " _
& "OR OEM61 LIKE 'strCriteria' OR OEM62 LIKE 'strCriteria' OR OEM63 LIKE 'strCriteria' OR OEM64 LIKE 'strCriteria' OR OEM65 LIKE 'strCriteria' OR OEM66 LIKE 'strCriteria' OR OEM67 LIKE 'strCriteria' OR OEM68 LIKE 'strCriteria' OR OEM69 LIKE 'strCriteria' OR OEM70 LIKE 'strCriteria " _
& "OR OEM71 LIKE 'strCriteria' OR OEM72 LIKE 'strCriteria' OR OEM73 LIKE 'strCriteria' OR OEM74 LIKE 'strCriteria' OR OEM75 LIKE 'strCriteria' );"

strSQL = strSQL & " WHERE " & strSQLWhere

'Used for testing SQL String
'debug.print strSQL
lngCount = FindRecord(strSQL)
If lngCount = 0 Then
   ExecuteSearch = False
Else
   ExecuteSearch = True
   Set rst = me.RecordsetClone
   DoCmd.ApplyFilter (strSQL)
End If

Exit_ExecuteSearch:
   Exit Function

Err_ExecuteSearch:
   MsgBox Err.Description
   ExecuteSearch = False
   Resume Exit_ExecuteSearch
End Function




Function FindRecord(ByVal strSearchString As String) As Long
On Error GoTo Err_Findrecord

Dim dbSearch as DAO.Database
Dim rsSearch as DAO.Recordset

Set dbSearch = DBEngine.Workspaces(0).Databases(0)
Set rsSearch = dbSearch.OpenRecordset(strSearchString, dbOpenSnapshot)

With rsSearch
   If (.BOF and .EOF) Then
      FindRecord = 0
   Else
      .MoveLast
      FindRecord = .RecordCount
   End If
End With

Exit_FindRecord:
   rsSearch.Close
   dbSearch.Close
   Exit Function

Err_FindRecord:
   MsgBox Err.Description
   Resume Exit_FindRecord
End Function

This is what it returns:

SELECT * FROM [REOEM] WHERE ID IN (SELECT ID FROM [REOEM] WHERE Manufacturer LIKE '*1155*' OR Type LIKE '*1155*' OR Make LIKE '*1155*' OR Lester LIKE '*1155*' OR OEM1 LIKE '*1155*' OR OEM2 LIKE '*1155*' OR OEM3 LIKE '*1155*' OR OEM4 LIKE '*1155*' OR OEM5 LIKE '*1155*' OR OEM6 LIKE '*1155*' OR OEM7 LIKE '*1155*' Or OEM8 Like '*1155*' Or OEM9 Like '*1155*'OR OEM10 LIKE '*1155*' OR OEM11 LIKE '*1155*' OR OEM12 LIKE '*1155*' OR OEM13 LIKE '*1155*' OR OEM14 LIKE '*1155*' OR OEM15 LIKE '*1155*' OR OEM16 LIKE '*1155*' OR OEM17 LIKE '*1155*' OR OEM18 LIKE '*1155*' OR OEM19 LIKE '*1155*' OR OEM20 LIKE '*1155*' OR OEM21 LIKE '*1155*' OR OEM22 LIKE '*1155*' OR OEM23 Like '*1155*' OR OEM24 LIKE '*1155*' OR OEM25 LIKE '*1155*' OR OEM26 LIKE '*1155*' OR OEM27 LIKE '*1155*' OR OEM28 LIKE '*1155*' OR OEM29 LIKE '*1155*' OR OEM30 LIKE '*1155*' OR OEM31 LIKE '*1155*' OR OEM32 LIKE '*1155*' OR OEM33 LIKE '*1155*' OR OEM34 LIKE '*1155*' OR OEM35 LIKE '*1155*' OR OEM36 LIKE '*1155*' OR OEM37 LIKE '*1155*' OR OEM38 LIKE '*1155*'
OR OEM39 LIKE '*1155*' OR OEM40 LIKE '*1155*' OR OEM41 LIKE '*1155*' OR OEM42 LIKE '*1155*' OR OEM43 LIKE '*1155*' OR OEM44 LIKE '*1155*' OR OEM45 LIKE '*1155*' OR OEM46 LIKE '*1155*' OR OEM47 LIKE '*1155*' OR OEM48 LIKE '*1155*' OR OEM49 LIKE '*1155*' OR OEM50 LIKE '*1155*' OR OEM51 LIKE '*1155*' OR OEM52 LIKE '*1155*' OR OEM53 LIKE '*1155*' OR OEM54 LIKE '*1155*' OR OEM55 LIKE '*1155*' OR OEM56 LIKE '*1155*' OR OEM57 LIKE '*1155*' OR OEM58 LIKE '*1155*' OR OEM59 LIKE '*1155*' OR OEM60 LIKE '*1155*' OR OEM61 LIKE '*1155*' OR OEM62 LIKE '*1155*' OR OEM63 LIKE '*1155*' OR OEM64 LIKE '*1155*' OR OEM65 LIKE '*1155*' OR OEM66 LIKE '*1155*' OR OEM67 LIKE '*1155*' OR OEM68 LIKE '*1155*' OR OEM69 LIKE '*1155*' OR OEM70 LIKE '*1155*' OR OEM71 LIKE '*1155*' OR OEM72 LIKE '*1155*' OR OEM73 LIKE '*1155*' OR OEM74 LIKE '*1155*' OR OEM75 LIKE '*1155*' );

Any assistance would be greatly appreciated. Be gentle, I've only been at this about a month. Most of this code is shamelessly adapted from online sources.

At a glance the syntax seems fine. Can you run the SQL statement that is returned? If not, are you using the same table for both departments? If it's not the same table, are you positive all the fields are the same? — Steve
– Steve, Commented Apr 11, 2018 at 19:09
it's not the same table, but I've ensured the field names are correct multiple times. The few starting off, then OEM1-OEM75 in sequence. The SQL returned from the Immediate window through debug.print won't run in Access' built in SQL builder, but I've gathered that it's because it won't process subqueries without them being embedded in the VBA code, unless I read wrong, which is entirely possible. — Underpaid
– Underpaid, Commented Apr 11, 2018 at 19:18
Please post exact error as it relays useful information. Also, you do not need a subquery: SELECT * FROM [REOEM] WHERE Manufacturer LIKE ... will work. Finally, very wide tables of like data such as OEM1-75 is suboptimal database design forcing complex queries. You could use one OEM field in long format with indicators 1-75 in adjacent column. And then search one column! Always remember columns are expensive but rows are cheap, no restructuring for new OEMs, OEM76-100. — Parfait
– Parfait, Commented Apr 11, 2018 at 19:50

SunKnight0 · Accepted Answer · 2018-04-11 19:34:47Z

1

Are you sure that is what you get? Because as far as I can tell

Manufacturer LIKE 'strCriteria' OR ...

should be

Manufacturer LIKE '" & strCriteria & "' OR...

Etc.

Consider this example:

Dim a As String
Dim b As String
a = "My Text"
b = "My text value is 'a'"

The resulting b is My text value is 'a'. not My text value is 'My Text'

edited Apr 11, 2018 at 19:34

answered Apr 11, 2018 at 19:14

SunKnight0

3,3611 gold badge13 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Underpaid Over a year ago

I was using that, but I found that... strCriteria = "" & Me.MainSearch.Text & "" results in the same output in the immediate window as concatenating inside the SQL string, and doing it once as opposed to 80 times leaves a lot less margin of error on my end, unless the program is going to see it differently?

Underpaid Over a year ago

Trying it anyways.

Underpaid Over a year ago

Yeah, output is the same.

SunKnight0 Over a year ago

It actually should not be. Manufacturer LIKE 'strCriteria' OR should output that exact string, not replace strCriteria with the contents of the strCriteria variable, unless you are actually doing a replacement that is not in the example code you posted.

Underpaid Over a year ago

HOWEVER, changing all that let me catch a mistype I'd missed. And now I feel like a total idiot. But it works! Thanks!

|

Parfait · Accepted Answer · 2018-04-11 20:34:02Z

Consider using parameterization, an industry best practice beyond MS Access but all databases used in application layer code. Doing so you avoid concatenating VBA values to SQL code with need of wrapping quotes or hashtags or other symbols depending on data types. Even too, a clever user can write malicious code inside the search box to ruin your database. Don't think all users are minimally computer literate!

MS Access SQL dialect allows the PARAMETERS clause. Then in VBA, use QueryDef.Parameters to bind values to named parameters. And update form with its Recordset property where zero returned rows renders as an empty form.

SQL query (save as an MS Access stored query)

PARAMETERS strCriteriaParam Text (255);
SELECT * FROM REOEM
WHERE Manufacturer LIKE strCriteriaParam OR [Type] LIKE strCriteriaParam 
OR [Make] LIKE strCriteriaParam OR Lester LIKE strCriteriaParam     
OR OEM1 LIKE strCriteriaParam OR OEM2 LIKE strCriteriaParam OR OEM3 LIKE strCriteriaParam 
OR OEM4 LIKE strCriteriaParam OR OEM5 LIKE strCriteriaParam OR OEM6 LIKE strCriteriaParam 
OR OEM7 LIKE strCriteriaParam OR OEM8 LIKE strCriteriaParam OR OEM9 LIKE strCriteriaParam 
OR OEM10 LIKE strCriteriaParam OR OEM11 LIKE strCriteriaParam OR OEM12 LIKE strCriteriaParam 
OR OEM13 LIKE strCriteriaParam OR OEM14 LIKE strCriteriaParam OR OEM15 LIKE strCriteriaParam 
OR OEM16 LIKE strCriteriaParam OR OEM17 LIKE strCriteriaParam OR OEM18 LIKE strCriteriaParam 
OR OEM19 LIKE strCriteriaParam OR OEM20 LIKE strCriteriaParam OR OEM21 LIKE strCriteriaParam 
OR OEM22 LIKE strCriteriaParam OR OEM23 Like strCriteriaParam OR OEM24 LIKE strCriteriaParam 
OR OEM25 LIKE strCriteriaParam OR OEM26 LIKE strCriteriaParam OR OEM27 LIKE strCriteriaParam 
OR OEM28 LIKE strCriteriaParam OR OEM29 LIKE strCriteriaParam OR OEM30 LIKE strCriteriaParam 
OR OEM31 LIKE strCriteriaParam OR OEM32 LIKE strCriteriaParam OR OEM33 LIKE strCriteriaParam 
OR OEM34 LIKE strCriteriaParam OR OEM35 LIKE strCriteriaParam OR OEM36 LIKE strCriteriaParam 
OR OEM37 LIKE strCriteriaParam OR OEM38 LIKE strCriteriaParam OR OEM39 LIKE strCriteriaParam 
OR OEM40 LIKE strCriteriaParam OR OEM41 LIKE strCriteriaParam OR OEM42 LIKE strCriteriaParam 
OR OEM43 LIKE strCriteriaParam OR OEM44 LIKE strCriteriaParam OR OEM45 LIKE strCriteriaParam 
OR OEM46 LIKE strCriteriaParam OR OEM47 LIKE strCriteriaParam OR OEM48 LIKE strCriteriaParam 
OR OEM49 LIKE strCriteriaParam OR OEM50 LIKE strCriteriaParam OR OEM51 LIKE strCriteriaParam 
OR OEM52 LIKE strCriteriaParam OR OEM53 LIKE strCriteriaParam OR OEM54 LIKE strCriteriaParam 
OR OEM55 LIKE strCriteriaParam OR OEM56 LIKE strCriteriaParam OR OEM57 LIKE strCriteriaParam 
OR OEM58 LIKE strCriteriaParam OR OEM59 LIKE strCriteriaParam OR OEM60 LIKE strCriteriaParam 
OR OEM61 LIKE strCriteriaParam OR OEM62 LIKE strCriteriaParam OR OEM63 LIKE strCriteriaParam 
OR OEM64 LIKE strCriteriaParam OR OEM65 LIKE strCriteriaParam OR OEM66 LIKE strCriteriaParam 
OR OEM67 LIKE strCriteriaParam OR OEM68 LIKE strCriteriaParam OR OEM69 LIKE strCriteriaParam 
OR OEM70 LIKE strCriteriaParam OR OEM71 LIKE strCriteriaParam OR OEM72 LIKE strCriteriaParam 
OR OEM73 LIKE strCriteriaParam OR OEM74 LIKE strCriteriaParam OR OEM75 LIKE strCriteriaParam

VBA (using AfterUpdate event of MainSearch textbox)

Private Sub MainSearch_AfterUpdate()    
    Dim qDef As QueryDef
    Dim rst As Recordset

    Set qDef = CurrentDb.QueryDefs("mySavedQuery")

    ' BIND PARAMETER TO VBA VALUE
    qDef!strCriteriaParam = "*" & Me.MainSearch.Text & "*"

    ' OPEN QUERYDEF'S UNDERLYING RECORDSET
    Set rst = qDef.OpenRecordset()

    ' UPDATE FORM RECORDSET BY QUERY OUTPUT
    Set Me.Form.Recordset = rst

    Set rst = Nothing
    Set qDef = Nothing
End Sub

Aside - as commented above, if you are able to do so, consider adjusting your table schema and avoid very wide tables for long tables which is arguably more maintainable, scalable, and efficient.

Collectives™ on Stack Overflow

Building a WHERE IN SELECT Subquery in Access VBA through concatenation

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related