[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-09-21
7
Medium Priority
?
287 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
  • 3
  • 3
7 Comments
 
LVL 49

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
Independent Software Vendors: 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 49

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 49

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

834 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