Query disappears from access when trying to add it to Excel spreasheet when adding a formula in query

On Excel I want to add a query from Access.  I can see the query I want to bring in but as soon as I add this to my query in design mode

PERCENT: Format(IIf(Nz([SumOfQTY_ORD],0)=0,0,1-([SumOfQTY_OPEN]/[SumOfQTY_ORD])),"Percent")

I'm no longer able to see the query that I want to bring into my spreadsheet.  Any ideas as to why?  It disappears from the select table pop-up for that query.
LUIS FREUNDAsked:
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.

PatHartmanCommented:
That is probably Excel trying to help you because it knows it is smarter than you.  Why do you need the query more than once?  Can you change the SQL manually to join to it?  I don't use Excel for data processing so I am not familiar with this interface.
0
LUIS FREUNDAuthor Commented:
I actually link to the table to get my date to update charts real time.  I've read you can change the SQL statement to have excel accept it, I just don't know how to convert it so excel is fine with it.  Here is my access sql statement:

SELECT [qry_%_SHORTAGE_SUB].ASSY_NO, [qry_%_SHORTAGE_SUB].ASSEMBLY_DESCRIPTION, [qry_%_SHORTAGE_SUB].[P/M], [qry_%_SHORTAGE_SUB].SHORTAGE_PN, [qry_%_SHORTAGE_SUB].SHORTAGE_DESCRIPTION, [qry_%_PART_SUB].SumOfQTY_ORD, [qry_%_PART_SUB].SumOfQTY_OPEN, Format(IIf(Nz([SumOfQTY_ORD],0)=0,0,1-([SumOfQTY_OPEN]/[SumOfQTY_ORD])),"Percent") AS [PERCENT], v_Parts_MinDate.MinOfON_DOCK_DATE
FROM ([qry_%_SHORTAGE_SUB] LEFT JOIN [qry_%_PART_SUB] ON [qry_%_SHORTAGE_SUB].SHORTAGE_PN = [qry_%_PART_SUB].PART_NUMBER) LEFT JOIN v_Parts_MinDate ON [qry_%_PART_SUB].PART_NUMBER = v_Parts_MinDate.PART_NUMBER;
0
PatHartmanCommented:
You should be able to create the query you need in Access.  Then link to the query rather than creating the query in Excel.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LUIS FREUNDAuthor Commented:
Yes Sir....that's exactly what I was trying to do.  The query is built in Access....then I'm trying to link the data in excel but it's not visible when I add the formula...crazy stuff!
0
PatHartmanCommented:
No, it actually makes sense.  I didn't realize you were adding the formula to the Access query.  The database engine you are working with is Jet (.mdb) or ACE(.accdb),  When you link to the database from Excel, you are NOT using Access.  You are using Jet or ACE.  Access  does not even need to be installed on your computer.  Access includes VBA which is an embedded language.  Jet and ACE play by the rules of SQL server.  Therefore, if you use a function in a query that is not an intrinsic SQL function, Jet/ACE cannot interpret it and therefore Excel doesn't show it to because you couldn't run it if you could see it.

Access is a rapid application development tool.  It is the front end program that creates the application.  It creates forms and reports and code.  Access IS NOT A DATABASE ENGINE.  Most people make that mistake because Access the RAD tool is frequently used as the GUI to create tables and queries for Jet and ACE although anything you can do with the Access GUI, you can do with DDL which is the Data Definition Language supported by all implementations of SQL.  Think of Access as SSMS for Jet and ACE.  The other confusing factor is that Access the RAD tool uses Jet or ACE to store its own objects.  So Access needs Jet/ACE for itself but for data, Access can use any RDBMS that supports ODBC.

I think  Jet/ACE might support the IIf() function but Nz() is VBA.
0
LUIS FREUNDAuthor Commented:
Thank you for the background...very helpful.

So if I modify the formula in access then this may work?
0
PatHartmanCommented:
Change the function to not use VBA functions.  I'm not sure what you options are on the Excel end.  You can create queries there and you might be able to use VBA functions be cause VBA is embedded in Excel.
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
LUIS FREUNDAuthor Commented:
Thanks!
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 Office

From novice to tech pro — start learning today.