"Like" expression in access query

I am currently using an query that uses the "like" expression.   Looks like this:

WHERE (((multiple4active.ShortTitle) Like "*" & [Enter a key word or Partial key word] & "*"));

I want to be about to pass a variable for a field name on a form were I currently have
"Enter a Key word or Partial key word"  How do I replace that with a field name from a form.
dbasetroublePurchasingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Like so:

WHERE (((multiple4active.ShortTitle) Like "*" & Forms!YourFormName!YourControlName & "*"));

mx
0
dbasetroublePurchasingAuthor Commented:
Didn't work something is missing.  Getting a syntax error.
0
kmslogicCommented:
Syntax looks good.  Of course you have to change YourFormName to the name of your form and YourControlName to the actual name of your control AND the form needs to be open when you are running the query.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This syntax is correct.  
1) Is your Form open when you run the query?
2) Did you change YourFormName to the actual name of your Form ... same for control.?

WHERE multiple4active.ShortTitle Like "*" & Forms!YourFormName!YourControlName & "*"  

Post the entire SQL you have ...

mx
0
Jeffrey CoachmanMIS LiasonCommented:
<Didn't work something is missing.  Getting a syntax error.>
Then why not post what you tried...
It is difficult to troubleshoot a syntax error if you don't post your syntax...
Make sense...?

Besides, you did not post any specifics on this mysterious "variable for a field name on a form"
...is it on the current form or an external form?
Is it truly a "variable", or just a reference...?
is this a real query, or are you building the SQL in code?

In any event, if this is only one criteria, then try it without the Parenthesis:
WHERE multiple4active.ShortTitle LIKE "*" & me.SomeField & "*"
...Or
WHERE multiple4active.ShortTitle LIKE "*" & Forms!SomeForm!SomeField & "*"
...or
WHERE multiple4active.ShortTitle LIKE "*" & strSomeFiled & "*"
...et al


JeffCoachman
0
dbasetroublePurchasingAuthor Commented:
screenshots.docThe access query name is keyword2.  The source is database multiple4active.  The field in the source is shorttitle.  The form name is test.  The field name on the form is word.  I have attached some screen shots.
0
Jeffrey CoachmanMIS LiasonCommented:
Again,...If it were me I would fist dump the parenthesis...

From your screenshot you just have a "Control" there (not really a "Field" or a "variable")
It's name (based on the label caption) is "Text0".

So I am confused when you say
"The field name on the form is word. "
...if you say so...

(This is why posting a sample database make this all *much* clearer)


So something like this should work:
WHERE multiple4active.ShortTitle LIKE "*" & Forms!me!Text0 & "*"
...or
WHERE multiple4active.ShortTitle LIKE "*" & Forms!me!word & "*"


Note that the form must be open and the value must be in the textbox when the query is run.
0
Jeffrey CoachmanMIS LiasonCommented:
Here is a simple sample of how a system like this works typically:
Database74.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Based on the screenshot, your table name (in the FORM clause) is brtrack ... not multiple4active.

So, you WHERE clause should be:

WHERE brtrack.ShortTitle Like "*" & Forms!test!word & "*"  

And I'm not clear on what you mean by 'source' in The source is database multiple4active  ?
0
dbasetroublePurchasingAuthor Commented:
Sample was great.  Worked fine.  The code was the trick.  I was originally  running the query on a macro.  Running it from code using the DoCmd seem to do the trick.
Thanks for the help.
0
dbasetroublePurchasingAuthor Commented:
I am a visual learner.  This was great.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
??
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
dbasetrouble
The original WHERE clause you posted was using the wrong table name ...
0
kmslogicCommented:
Move along, nothing to see here.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Huh?
0
Jeffrey CoachmanMIS LiasonCommented:
This is another example where a sample database would have cleared this all up in one post.

I don't see a reason why running the query from a macro and my method would make any difference...
I think that you may have had the wrong Field names and MX stated....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.