[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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

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
BBlu
Asked:
BBlu
  • 3
  • 3
1 Solution
 
Dale FyeCommented:
in a query, the syntax would normally be:

Like "*" & [Parameter]
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
BBluAuthor Commented:
Like this?  It's not working
 Like Parameter Syntax
I get the following error:
 Error: Too few parameters
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
BBluAuthor Commented:
Thanks, JeffCoachman.  I figured that would be the case.  But for some reason that syntax is giving me an error.
0
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
 
BBluAuthor Commented:
worked PERFECTLY!  Thank you both.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now