Access 2010 SQL issue

Hi Experts,
I am trying to assign the following sql to a RowSource property of a combo box and its giving me the attached error.
FYI- A-Its an ADP, B- this issue is only happening in Access 2010, while in 2000/3 it works fine.
SELECT TOP (99.9999) PERCENT EmployeeID, ContactPersonPhone, ContactPerson ,ContactPersonPhone,FacilityName,StatusDate,LastName,FirstName FROM dbo.EmployeesReferences INNER JOIN dbo.Employeestbl ON dbo.EmployeesReferences.EmployeeID = dbo.Employeestbl.ID where ContactPersonPhone is not null And ContactPersonPhone Like '212%' Order by ContactPersonPhone

Open in new window

Untitled.png
image002.png
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
Top 99.9999 will give you all the records, ...so why even bother with the TOP statement?

In any event, ...check all of your table and field names *carefully*
Robert ShermanOwnerCommented:
Is this only happening when you try to use this query as a combo-box rowsource?  If you take the same SQL statement and copy it into the SQL view of a Query, does it run and produce the expected results?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried removing the parentheses? If I test that syntax in an Access query, I get a syntax error, but if I remove the parentheses it works.

I've also seen this when my combo was hosed - essentially, the error is telling you that it's looking for an Object, when it should be using that RowSource. Try removing the combo and inserting a new one, and resetting your properties.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bfuchsAuthor Commented:
Hi Experts,

First thanks for replying..

@Jeff
The top 9999 was used since I had experienced some views didn't perform 100% accurate sorting without that 9999.

@Robert,
Yes, when I copy that to SSMS it works, besides this works in earlier versions.

 @Scott,
Tried without the top clause and still same problem.
Also tried creating a new combo box and assigning this SQL and didn't work either.

@All,
I realized in error message it adds a number 1 to the end of the SQL, Perhaps this is the culprit..

Thanks,
Ben
Robert ShermanOwnerCommented:
You have ContactPersonPhone in the select columns more than once, which might be why it's adding the 1 at the end of the error message, and could very well be the cause of the problem.

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
bfuchsAuthor Commented:
@Robert,
You got it!
Thank you.
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.