Solved

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

Posted on 2011-09-21
7
242 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 350 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

932 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

12 Experts available now in Live!

Get 1:1 Help Now