Solved

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

Posted on 2011-09-21
7
259 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
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…

821 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