?
Solved

MS Query in Excel that pulls from MS Access DB/Table

Posted on 2011-09-21
7
Medium Priority
?
275 Views
Last Modified: 2012-05-12
I've successfully created an MS Query in Excel that links to a MS Access Table.  I was then able to add parameters (one for date and one for name), then link it to an editable cell.  I'm trying now to change the parameter from an exact match (ie. [parameter]) to a like match (ie. like *[parameter]).  I don't know and can't get to work the correct syntax.  Can anyone help?
0
Comment
Question by:BBlu
[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
  • 3
  • 3
7 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36576568
in a query, the syntax would normally be:

Like "*" & [Parameter]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36576732
<No Points wanted, as fyed has answered your Q directly>

As I am sure you know, there are many variation of wild card usage.
So make sure that your app informs them that the wildcard is the fist character.

The users may simply try typing in the characters they are sure of.

For example: "ER"
...to get anything that contains "ER"
In this case a wildcard like (no pun intended)
   Like "*" & [Paramaeter] & "*"
...might be more fitting

JeffCoachman
0
 

Author Comment

by:BBlu
ID: 36576826
Like this?  It's not working
 Like Parameter Syntax
I get the following error:
 Error: Too few parameters
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BBlu
ID: 36576892
Thanks, JeffCoachman.  I figured that would be the case.  But for some reason that syntax is giving me an error.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36577424
It has been quite a while since I've created a query in Excel against an Access database.  It appears, at least with Excel 2007 that the syntax is:

Like '%[Parameter]%'

Single quote
% instead of * (like SQL Server)
and no & to concatenate the strings.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1400 total points
ID: 36577453
Well, it accepted that syntax, and returned records, but they were not the correct records.

But this sems to work?

Like '%' & [Parameter] & '%'

So, as Jeff indicated, if you want a "Contains" query, use that

If you want Begins with, use:  Like [Parameter] & '%'

and if you want Ends with, use: Like '%' & [Parameter]
0
 

Author Closing Comment

by:BBlu
ID: 36577506
worked PERFECTLY!  Thank you both.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 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