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 minimal reproducible example and value used in Me.txtkeywords. — Parfait
– Parfait, Commented May 17, 2021 at 17:56
Are the "problem" fields numeric? stackoverflow.com/questions/1108171/… & "OR [Card Lookup 1] & '' LIKE '*" & Me.txtkeywords & "*' " _ — Tim Williams
– Tim Williams, Commented May 17, 2021 at 18:04

Kaii · Accepted Answer · 2021-05-17 18:12:17Z

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, 2021 at 18:12

answered May 17, 2021 at 18:05

Kaii

20.6k3 gold badges40 silver badges63 bronze badges

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

3 Comments

James Fleming Over a year ago

Thank you for seeing the space. I have no idea about the debug console. First time really using Access...

Kaii Over a year ago

@JamesFleming see Where does VBA Debug.Print log to?

Kaii Over a year ago

@JamesFleming if you liked this answer, please consider accepting it. Thanks!

Tim Williams · Accepted Answer · 2021-05-17 18:34:20Z

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, 2021 at 18:34

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

6 Comments

James Fleming Over a year ago

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.

Tim Williams Over a year ago

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?

James Fleming Over a year ago

In design view they are listed as number with a field size of long integer.

Tim Williams Over a year ago

Are you using keywords match the "id" of your lookup, or the "value" ?

James Fleming Over a year ago

I dont think I fully understand the question. However, I think it should be the value.

|

Collectives™ on Stack Overflow

Access DB. VBA Query

2 Answers 2

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related