Solved

"Like" expression in access query

Posted on 2012-03-23
16
399 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
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Like so:

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

mx
0
 

Author Comment

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

Expert Comment

by:kmslogic
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Comment Utility
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
Comment Utility
Here is a simple sample of how a system like this works typically:
Database74.accdb
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
I am a visual learner.  This was great.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
??
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
dbasetrouble
The original WHERE clause you posted was using the wrong table name ...
0
 
LVL 16

Expert Comment

by:kmslogic
Comment Utility
Move along, nothing to see here.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Huh?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now