Excel Table Linked To Access Query

I have an Excel table linked to an Access query, Microsoft Office 365 ProPlus.  In the Access query I am eliminating certain records based on <> Criteria.  The record count from the query is 62 records.  When I refresh the Excel table, the record count is 75 records.  The 13 different count is due to the <> Criteria in the Access query.

What am I missing here?  Why is the Excel linked table bringing the records across that the Access query is eliminating?  I'm just not getting it.  Where might I be going wrong?  Thanks.  - Tom
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.

Jeffrey CoachmanMIS LiasonCommented:
Perhaps when you refresh Excel, ..."all" the records are being brought in again.
If you refresh excel, ...then close and re-open the Access query, ...is the recordcount back to 62?
0
tomfarrarAuthor Commented:
Record count is still 75.  The database resides on a company shared drive, and my spreadsheet is on my desktop.  Didn't seem to be a problem earlier.
0
PatHartmanCommented:
Check the criteria to make sure it is still there.
Close the query.
Relink the spreadsheet.
Run the query - is the criteria being applied?
You should see 75 records if you open the linked sheet in datasheet view but only 62 if you open the query.
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

tomfarrarAuthor Commented:
The criteria is still there.  There are actually 918 records in the linked spreadsheet.  There are 3 criteria using "AND" in the Access query, with two of them working, but not the third in the linked spreadsheet.  

I did relink the spreadsheet starting from scratch, and the Access query shows 62 records and the linked spreadsheet shows 75 records.  The difference is the "Not like INT*" in the Access query.

You've given me some things to think about.  I am going to try one criteria at a time in the query to see if that works with the spreadsheet.  Back soon.
0
PatHartmanCommented:
There are 3 criteria using "AND" in the Access query, with two of them working, but not the third in the linked spreadsheet
I am confused by this statement.  I thought all the criteria were in the Access query.

Try applying the criteria one at a time so see if they are working as you expect.
0
tomfarrarAuthor Commented:
Hmmmmm..  The Excel spreadsheet linked to the Access query works with the first two criteria, but not the third when I use "Like*", or "Not Like*" in the query.  The Like and Not Like work in the Access query, but do not carry over to the Excel table.  However, when I us the entire code criteria in the Access query "In (INT1, INT2, INT3)" the query works in Access and the correct record count carries over to Escel.  What is this about?
0
tomfarrarAuthor Commented:
The criteria are in the Access query.  The results of two of the criteria successful carry over to the linked spreadsheet.  The third criteria (Like*) works for the Access query results but does not carry over to the spreadsheet.  But when I change the Like* to a list the criteria works for Access and for Excel.
0
PatHartmanCommented:
The Like and Not Like work in the Access query, but do not carry over to the Excel table.
I don't know what this statement means.  Are you trying to run the Access query from Excel?  That doesn't make sense when the Access query is looking at the linked spreadsheet and I doubt you would even "see" that particular query in Excel anyway.  Excel only "sees" queries it can run.  You said you had an Access query that includes the three criteria and it works correctly on the linked spreadsheet.  I do not understand what problem you are having.  What does "carry over" mean?  Please try to use precise, technical terms to describe exactly where the error occurs.
0
tomfarrarAuthor Commented:
Okay, first of all, the problem is fixed.  "Like" and "Not Like" are expressions in the Access query that narrows the query selection to records like or not like the criteria (I know you know this, but...)  For instance if my Access tables of names included:

Carr
Smith
Jones
White
 
and I used Like "C", the query would return "Carr".  Likewise if I used Not Like "C" in the criteria, the query would return "Smith", "Jones", "White".  Right?

What I was saying is the Access query worked with the "Like" and "Not Like" criteria and filtered the output accordingly.  But when I refreshed the spreadsheet linked to the Access query, the criteria of Like and Not Like did not work.

However, when I stopped using Like and Not Like in the Access query and replaced it with a specific list of criteria, the output in the Access query and the output in the linked spreadsheet agreed.  Don't ask me why...
0
PatHartmanCommented:
Did you close the query after/before you refreshed the link?  And then rerun the query?  If you have the query open, refreshing the link will not cause the query to rerun.  You need to close it and reopen it.  You could press the refresh button but that will not pick up new records.  You actually have to close the query and open it again.
0
tomfarrarAuthor Commented:
I closed and reopened the query multiple times.  I stand by what I said until someone proves me wrong.  Strange as it is the query output only was reflected correctly in the linked pivot table after I changed the criteria from Like "INT*" to In (INT1, INT2).   Since I have solved the issue this way, I am okay with closing this question.  I will leave it open for a few days to see if anyone else has a comment, then I will close the question.  Thanks both of you for the input.  - Tom
0
PatHartmanCommented:
I am not an Excel user so I don't have much experience with running Access queries from Excel.  I'm going to guess that because the Like something* didn't work correctly, that Excel is set to use SQL Server Wildcards rather than Access wild cards.  Just to see if that is the case, try the same query using % rather than *.  If that works, you should track down the setting in Excel that is causing the problem.  In Access, the setting says to use SQL92 and that makes it act more like SQL Server.
1

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:
Yes, Pat, that seems to be the problem.
0
tomfarrarAuthor Commented:
I'd like to award the points to Pat.  She is correct in her response today.
0
PatHartmanCommented:
see ID: 41704056
0
tomfarrarAuthor Commented:
Thanks. Pat.
0
PatHartmanCommented:
You're welcome.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.