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?
 
PatHartmanConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
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
 
LUIS FREUNDAuthor Commented:
Thanks!
0
 
PatHartmanCommented:
You're welcome.
0
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.

All Courses

From novice to tech pro — start learning today.