We help IT Professionals succeed at work.
Troubleshooting Question

Access 2016 Select Data Matching Key Word From Text Field

Bob Collison
Bob Collison asked
on
36 Views
Last Modified: 2020-08-27

Hi Experts,


In Access 2016 I have a Text Field (SCENE_KEY_WORD ShortText 250) that contains various words separated by a space.
E.g. Gord Wedding

I have the following VBA Code to select data where the Key Word supplied as a parameter ParmSceneKeyWordSelection110 (containing Gord).
Cmd00SceneMstrLkup110 = "SELECT * " & _
                        "FROM 00_SCENE_MSTR " & _
                        "WHERE SCENE_SRC_TYPE_KEY Like " & DblDblQuote & ParmSceneSourceType110 & DblDblQuote & "And " & _
                              "SCENE_SRC_NBR Like " & DblDblQuote & ParmSceneSourceNumber110 & DblDblQuote & "And " & _
                              "SCENE_CATEGORY_KEY Like " & DblDblQuote & ParmSceneCategory110 & DblDblQuote & "And " & _
                              "SCENE_KEY_WORD Like " & DblDblQuote & ParmSceneKeyWordSelection110 & DblDblQuote

I want the above to select all records that have 'Gord' in the SC ENE_KEY_WORD field.

Its not selecting any data.

What do I need to do to get it to work?

Thanks,
Bob C.

Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
I don't know what's in DblDblQuote, but I'm guessing the end result is a string of two double quotes...

If that is the case then I would suggest using single quotes in the query, easier to work with.

Also, for a LIKE operator, unless you want a match to the input values, you will need to add asterickls to the beginning and end of the LIKE match pattern.

So perhaps something like:

Cmd00SceneMstrLkup110 = "SELECT * " & _
                        "FROM 00_SCENE_MSTR " & _
                        "WHERE SCENE_SRC_TYPE_KEY Like '*" & ParmSceneSourceType110       & "*' And " & _
                              "SCENE_SRC_NBR      Like '*" & ParmSceneSourceNumber110     & "*' And " & _
                              "SCENE_CATEGORY_KEY Like '*" ParmSceneCategory110           & "*' And " & _
                              "SCENE_KEY_WORD     Like '*" & ParmSceneKeyWordSelection110 & "*'"

Open in new window


»bp
Bob CollisonSystem Architect

Author

Commented:
Hi Bill,

Thanks for the quick response.

Here is the definition of the DblDblQuote that was originally suggested to be used by one of the EE Experts.
Option Compare Database
Option Explicit
Public Const DblDblQuote = """"  'Use to replace Chr$(34)

I simply commented out my code and copied in your code and it turned 'red' with a messsgae of 'Syntax Error' which I can't figure out.

Thanks.
Bob C.
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob CollisonSystem Architect

Author

Commented:
Hi Bill,

I have copied in your fixed version and it doesn't select anything.

I also simplified the code to the following to ensure that there isn't anything else affecting the code.

Cmd00SceneMstrLkup110 = "SELECT * " & _
                        "FROM 00_SCENE_MSTR " & _
                        "WHERE SCENE_KEY_WORD     Like '*" & ParmSceneKeyWordSelection110 & "*'"

While I have been waiting for a solution I tried googling other methods and came across the VBA Split Function.  Is it a better method?  I have a problem understanding array syntax so I haven't tried that option.

Thanks,
Bob C.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Try displaying the Cmd00SceneMstrLkup110 variable after you have built it.  Here's what I just did in a quick test here and it seems to work, unless your variable doesn't contain "Gord"?  I took the SQL command right out of the immediate window, pasted it into the SQL view of a query, and you can see the results, where it did find the record that matched.

sshot-387.png

»bp
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
you might want to take a look at my article on Complex Text Filters
Bob CollisonSystem Architect

Author

Commented:
Hi Bill,

I don't know how to use the Immediate Window.  I do have a GoTo that I stopped on immediately following the Cmd code and the Parameter Field has "Gord" not "*Gord*"

Thanks,
Bob C.

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Split keyword is the solution if you wrap it's functionality in a function and use it to produce a delimited string
So your SQL would be something like
Select * from YourTable Where SomeField IN ('Val1','Val2",...)
Assuming you have a user function that reconstructs your SQL according to the input.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Okay, that seems reasonable.  Stop the code after the assignment to the Cmd00SceneMstrLkup110 variable and see what it's value is then, if it looks right.  If it does then it may be the way you are executing that query, or your data doesn't contain that row...


»bp
Bob CollisonSystem Architect

Author

Commented:
Hi John,

Thanks for the comment.  Unfortunately I don't have any idea what you are suggesting.

Thanks,
Bob C.
Bob CollisonSystem Architect

Author

Commented:
Hi Experts,
This application is quite small so I will package it up and add it to the Question.

The code can be found in the F-00-110 - Image Management form as Function:
Private Sub PrepareImageReportData01(F00110ParmReportKey As String, F00110ParmReportName As String)

The code in question is in 'Step 160-Apply Report Filter Criteria Data and is the code that is currently uncommented.

I was able to figure out the Immediate Window and it displays the same data as Bill indicated it should.

ImageDB.accdb

If you use the first selection criteria it selects data so the issue doesn't appear to be data related.

Thanks,
Bob C.
Bob CollisonSystem Architect

Author

Commented:
Hi Experts,

My Bad!
Once I had the correct syntax (from Bill Prew) I finally realized that the Field Name I was searching was specified incorrectly.  i.e. SCENE_KEY_WORD instead of the correct SCENE_KEY_WORDS.  I missed the 'S' at the end of 'WORD'.

Thanks for fall your effort to solve this issue.
Bob C.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Great, glad you got that sorted out.

If you aren't doing it, always use:

Option Explicit

Open in new window

at the top of your modules, it can help sometimes identify typos like this (not always, but if you reference a variable that isn't defined it will not compile).



»bp
Bob CollisonSystem Architect

Author

Commented:
Hi Bill,

I at the top of every Module.

Thanks for the advice.
Bob C.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.