How do I do use a wildcard in a "Microsoft Query" embedded in an excel spreadsheet ?

Hi,

I'm trying to help the users of a spreadsheet which was created by my predessor in my current job (who has since returned to India and is unreachable).

He set up a query from this spreadsheet by following these steps: Data Tab, Get External Data Group, From other Sources, From Microsoft Query. (The query he set up pulls from an Access database, which I maintain). They query by doing right click, refresh, then entering a parameter. Right now the query requires the user to put in the exact value they want to search on. The users want me to alter this query so they can use wildcards in the parameter value they enter.

I've tried going into Data Tab, Connections, Properties, Details to change the query, but I don't know what to put in as a wildcard character so that the users can enter the first few characters then pull in everything that begins with those characters. (~, ?, and * aren't working, unless I'm using them incorrectly)

What is the best way to set this up for them

I very much appreciate any thoughts you have on this.

Thanks

Steve

You have changed the condition from = value to like value*?

Can you give me an example of the syntax I would use for "like value*" ?

There are about 20 users all using this spreadsheet during the day, each day. Each user will enter a different value when they use the spreadsheet.

Values are 10-20 length alphanumeric characters/numbers.

If the user wants to find all values which begin with, for example, a value of DHEC* how would they enter this in the field ? or how would I enter it in the query ?

Thanks alot, I really appreciate your advice

OK, I went into connections, Definition, and changed the query to do a Like. Then I go to the spreadsheet which runs it and enter DHEC* and it seems to consider it an exact match and brings back nothing when I know that this value exists in the database.

This is the query:

SELECT `Data CitiFin SSB Voice Master`.`Source Master`, `Data CitiFin SSB Voice Master`.source, `Data CitiFin SSB Voice Master`.`Vendor Name`, `Data CitiFin SSB Voice Master`.`Circuit/TN`, `Data CitiFin SSB Voice Master`.`Total Charges`, `Data CitiFin SSB Voice Master`.InvoiceDate, `Data CitiFin SSB Voice Master`.`Circuit Status`, `Data CitiFin SSB Voice Master`.`Expense Code`
FROM `Data CitiFin SSB Voice Master` `Data CitiFin SSB Voice Master`
WHERE (`Data CitiFin SSB Voice Master`.`Circuit/TN` like ?)


I really appreciate the assistance.

Alan thanks for your assistance.

I can't get to the first link, its showing up with dots in this post. For some reason though I am able to get to the second link.

Ok, so I went into the area where I set a prompt and I put the following into the prompt (without quotes):

"Please enter the first 3 characters of the circuit id followed by an asterix"

Then I ran the query, and entered DHE*, which I know exists in the database. It brought back nothing, which I assume is because it was looking for an exact match and didn't find anything looking like "DHEC*" (without the quotes)

That word was probably blocked by this site because in American English it would be considered innappropriate for an office or professional setting lol.
The meaning must be different in British usage.

I did add "like" into my prompt.

I tried the % sign instead of an * and it gives me an error message

I can't put that URL in my browser without getting hauled off to human resources lol. As well as the fact that it woul earn me an image for me that is fine for others who are that way, but id just is not who I am, lol.

So, I used like [name] = % in my query, as described in the second link, and it brought back everything without asking for the first part of the string.