Access DB. VBA Query

Question

I am trying to figure out why the following code wont return anything from [Card Lookup 1], 2 or 3. I know some very basic SQL and virtually no VBA. I appreciate any assistance.

Private Sub btnSearch_Click()
Dim SQL As String
    SQL = "SELECT [Master List].[First Name], [Master List].[Last Name], [Master List].[Card Lookup 1], [Master List].[Card Lookup 2], [Master List].[Card Lookup 3], [Master List].ID " _
    & "FROM [Master List]" _
    & "WHERE [First Name] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Last Name] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Card Lookup 1] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Card Lookup 2] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Card Lookup 3] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR ID LIKE '*" & Me.txtkeywords & "*' " _
    & "ORDER BY [Master List].[Last Name] "
    
    Me.subUserSearch.Form.RecordSource = SQL
    Me.subUserSearch.Form.Requery

End Sub

[First Name] = Short Text
[Last Name] = Short Text
[Card Lookup 1] = Number
[Card Lookup 2] = Number
[Card Lookup 3] = Number
[ID] = AutoNumber

Expected: User inputs name or number associated with a card Returns match based on name or number entered. In the original code it returns every field fine except Card Lookup 1, 2, or 3 Upon changing the wildcard symbol, it fails to return any data.

Card Lookup 1,2 and,3 are assigned IDcard Numbers.

Please provide sample data of *Card Lookups* for [mcve] and value used in `Me.txtkeywords`. — Parfait, May 17 '21 at 17:56
Are the "problem" fields numeric? https://stackoverflow.com/questions/1108171/how-to-use-like-condition-in-sql-with-numeric-field `& "OR [Card Lookup 1] & '' LIKE '*" & Me.txtkeywords & "*' " _` — Tim Williams, May 17 '21 at 18:04

Kaii · Answer 1 · 2021-05-17T18:12:17.907

1

There is a space missing:

& "FROM [Master List]" _
& "WHERE [First Name] LIKE '*" & Me.txtkeywords & "*' " _

... results in:

FROM [Master List]WHERE [First Name] LIKE '*foo*'

Insert space here:

& "FROM [Master List] " _

Do you know the debug console in VBA editor? For debugging the first step is always looking at the results:

debug.print SQL

Also, you might want to add error handling so you can see the actual error message causing your empty results in the debug console:

Private Sub xxx()
on error goto fErr

' query action here

fExit:
' do your cleanups here
exit sub

fErr:
debug.print err.description
resume fExit
End Sub

edited May 17 '21 at 18:12

answered May 17 '21 at 18:05

Kaii

20,122
3
38
60

Thank you for seeing the space. I have no idea about the debug console. First time really using Access... – James Fleming May 17 '21 at 18:17
@JamesFleming see [Where does VBA Debug.Print log to?](https://stackoverflow.com/questions/2916287/where-does-vba-debug-print-log-to) – Kaii May 17 '21 at 18:18
@JamesFleming if you liked this answer, please consider accepting it. Thanks! – Kaii Oct 21 '21 at 21:14

score 1 · Answer 2 · answered May 17 '21 at 18:34

1

As noted here: How to use LIKE condition in SQL with numeric field?

You can't use Like with a numeric field without casting the value to a string (eg. by concatenating its value with '')

Private Sub btnSearch_Click()
Dim SQL As String
    SQL = "SELECT [Master List].[First Name], [Master List].[Last Name], [Master List].[Card Lookup 1], [Master List].[Card Lookup 2], [Master List].[Card Lookup 3], [Master List].ID " _
    & "FROM [Master List] " _
    & "WHERE [First Name] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Last Name] LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Card Lookup 1] & '' LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Card Lookup 2] & '' LIKE '*" & Me.txtkeywords & "*' " _
    & "OR [Card Lookup 3] & '' LIKE '*" & Me.txtkeywords & "*' " _
    & "OR ID LIKE '*" & Me.txtkeywords & "*' " _
    & "ORDER BY [Master List].[Last Name] "
    
    Me.subUserSearch.Form.RecordSource = SQL
    Me.subUserSearch.Form.Requery

End Sub

answered May 17 '21 at 18:34

Tim Williams

154,628
8
97
125

Thank you. I understand how that is supposed to work. After applying the changes. I enter "371". It correctly returns values in the ID field that contain "371" but still does not return anything from the Card Lookup fields. – James Fleming May 17 '21 at 18:49
I'm not an access user, so maybe there's something specific to your "lookup" fields which is an issue here? Are they just "regular" numeric fields? – Tim Williams May 17 '21 at 19:00
In design view they are listed as number with a field size of long integer. – James Fleming May 17 '21 at 19:09
Are you using keywords match the "id" of your lookup, or the "value" ? – Tim Williams May 17 '21 at 20:29
I dont think I fully understand the question. However, I think it should be the value. – James Fleming May 19 '21 at 18:21
The "id" is typically a numeric value (1,3,5, etc) whereas the "value" would typically be a text value ("red", "green", "blue") etc. If you want to query on the text values then you need to join the lookup table(s) in your query SQL. – Tim Williams May 19 '21 at 18:23

Access DB. VBA Query

2 Answers2