asked on

Access 2016 Select Data Matching Key Word From Text Field

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.

Bill Prew

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 Collison

ASKER

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.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Collison

ASKER

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 Prew

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.

»bp

Dale Fye

you might want to take a look at my article on Complex Text Filters

Bob Collison

ASKER

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 Tsioumpris

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 Prew

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 Collison

ASKER

Hi John,

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

Thanks,
Bob C.

Bob Collison

ASKER

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 Collison

ASKER

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 Prew

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 Collison

ASKER

Hi Bill,

I at the top of every Module.

Thanks for the advice.
Bob C.