Access "Like" criteria doesn't seem to work for output to linked Excel table..

I have attached three documents:

Employee Access DB

Employee Spreadsheet (linked into the DB as Employee Table)

Output.xlsx spreadsheet that gets output from the Employee Qry in the DB.

When I use the Like 12* to select records beginning with 12, the query works fine in the DB, but the records don't show up in the Output.xlsx spreadsheet.  Not sure why.  If I use In(123,124..Etc) the query works fine in Access and the records show up in the Output.xlsx spreadsheet.  Why doesn't the "Like" expression work for the Output.xlsx spreadsheet?  Thanks.  - Tom
Employee-Database.accdb
Employee-Spreadsheet.xlsx
Output.xlsx
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
how do you create the output.xlsx?
0
Rgonzo1971Commented:
Hi,

You could use

Left$([EMPLOYEE TABLE].[Employee Code],2)=12
AS criteria

or

Like "12%"

does not work in Access but in XL

Regards
0
PatHartmanCommented:
1. There is nothing in the database that indicates how you are exporting the records to Output.xlsx and I have asked you more than once to explain what you are doing in your other thread.
2. The query in the database includes NO criteria which explains why your export does not produce the desired results.
3. When I add criteria, I get the expected results.

Just FYI - Employee Code is defined as numeric.  LIKE is a string operation.  It will not always work as you expect when used for numeric fields.  The use of LIKE in this situation is symptomatic of a table design flaw.  You are assigning some meaning to the first two positions of Employee Code and that means that the Employee Code is not atomic and therefore violates first normal form.  Your best solution at this stage is to create a separate field to hold whatever "12" is.  Then select on that.  Using LIKE will in most cases prevent the query engine from using any index to select the requested rows.  So, in essence, you are forcing a full table scan which is significantly more costly than using an index and if the table ever grows to more than a few thousand rows, the price will be clear.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tomfarrarAuthor Commented:
Good Morning, Pat, I hope your day is going well.  The answer to the first question is I linked the spreadsheet to the DB query in Excel by going to Data > (Get External Data) Access DB.  That walks me through to the query.  Regarding your second bullet point I assumed the Expert might put the criteria (as described) in the query since there were two options.  Regarding your third bullet point you get the expected results where?  In the Access DB?  So do I, but not in the Output.xlsx spreadsheet when I go to the Excel table and right click and "Refresh" it.
0
tomfarrarAuthor Commented:
Rey - Please see my comment above.  Sorry I missed your post.  Also Rgonzo1971, please hold that thought.
0
Rey Obrero (Capricorn1)Commented:
i don't know what you  were doing but it works here.

did you save the query with criteria Like "12*" before refreshing the data in excel?
0
tomfarrarAuthor Commented:
Hi Rey - Yes I did save the query before refreshing Excel.  You are saying the "Like" criteria gave you the correct results in both the Access query and the Output.xlsx linked spreadsheet, correct?  Because I am not getting that.  Only when I use the In() expression.  With "Like" I get the correct results in Access, but not Excel.

As for Pat's comment regarding my example 12, etc being a number, the file I sent was only an example.  The real data (where I have the same problem) was using "Like" on a text field where the data was INT01, INT02, INT03 and I was using (in the Access query) Like "INT*".  Again the results good in Access, but not Excel.
0
tomfarrarAuthor Commented:
Excel and Access versions:  Microsoft Office 365 ProPlus..
0
Rey Obrero (Capricorn1)Commented:
i tried it in Office 2007. could be different with office 365
0
PatHartmanCommented:
tomfarrar,
Finally, we know what you are trying to do.  However, it makes no sense to me.  Why don't you just export the query from Access?

Rgonzo1971 was on the right tract but didn't carry through.
You need to change Access to use the ANSI 92 syntax for SQL Server.  To do that go to Options/Object Designers.
ANSI 92 syntaxThen you have to change the query itself to use the ANSI 92 syntax.  In this case, use % instead of *.  If you need other wildcards, you can search for the set.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfarrarAuthor Commented:
I am not exporting the query from Access because I have users using Excel to import the data, Pat.  

I don't usually have an issue expressing myself to most of the experts.  

"Finally, we know what you are trying to do."

Guess this was just a bad day.
0
Rey Obrero (Capricorn1)Commented:
it would have been helpful if you posted the version of Office you were using in your original qusetion?
0
tomfarrarAuthor Commented:
Yes, in retrospect you are correct, Rey.  Thanks.
0
PatHartmanCommented:
@tomfarrar
I came up with the answer 45 minutes (at 12:18) before you did :) :)  I would have come up with it days ago in your other thread if you had only said that you were opening Excel and running the query from Access to import the data.

To those of us who work with Access, it makes absolutely no sense to do what you are doing.  It looks like a band aid because Excel can't import data from a different workbook and filter it on import.  You wouldn't be using Access at all if you could get Excel to do what you wanted.  Perhaps posting an Excel question might have come up with an Excel only solution.
0
tomfarrarAuthor Commented:
Thanks, Pat, your opinion is appreciated.  Congratulations for finally getting to where I hoped we'd be a few days ago.  I've become accustom to other experts asking questions early on in the process to lead us "not in the know" people down the right path.  But that didn't happen here as I see it.

 I'll try to structure my questions so they don't interfere with the fine work you are doing for others.  I sure don't want to take time away from your important work.  Thanks.  - Tom
0
PatHartmanCommented:
Count how many times I asked you in the other thread to explain how you were running this query and how I had to ask you again in this thread.  Nothing you said indicated you were attempting to run the query FROM Excel.  As you can see, that made a world of difference since Excel and Access don't use the same syntax for queries.

Now we all know the answer:)
0
tomfarrarAuthor Commented:
What can I say, Pat, you've left me speechless.  Thanks for your input.  Going forward I'd appreciate it if, you see a question from me, you just pass on by.   - Tom
0
PatHartmanCommented:
Your wish is my command.  It is also your loss since, once I had sufficient information, I came up with the solution.
0
tomfarrarAuthor Commented:
All - Thanks for the help, and most of the dialogue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.