Solved

"Like" expression in access query

Posted on 2012-03-23
16
403 Views
Last Modified: 2012-03-24
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.
0
Comment
Question by:dbasetrouble
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 75
ID: 37760023
Like so:

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

mx
0
 

Author Comment

by:dbasetrouble
ID: 37760476
Didn't work something is missing.  Getting a syntax error.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 37760512
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 75
ID: 37761040
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761056
<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
 

Author Comment

by:dbasetrouble
ID: 37761201
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37761290
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37761319
Here is a simple sample of how a system like this works typically:
Database74.accdb
0
 
LVL 75
ID: 37761895
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
 

Author Comment

by:dbasetrouble
ID: 37761905
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
 

Author Closing Comment

by:dbasetrouble
ID: 37761909
I am a visual learner.  This was great.
0
 
LVL 75
ID: 37761917
??
0
 
LVL 75
ID: 37761920
dbasetrouble
The original WHERE clause you posted was using the wrong table name ...
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 37762000
Move along, nothing to see here.
0
 
LVL 75
ID: 37762060
Huh?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37762063
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

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question