none
MS Query returns blank when the calculated average is zero RRS feed

  • Question

  • 

    I have a query in a cell that calculates the average of some numbers.  If the average is zero it returns a blank. How do I get it to return a zero?

    Here is the SQL view of the query:

    SELECT Avg(Qnotes.Age) AS 'Avg of Age'
    FROM Qnotes Qnotes
    WHERE (Qnotes.Program='LGMT') AND (Qnotes.Type='F3') AND (Qnotes.`P/N` Not Like '%GX%' And Qnotes.`P/N` Not Like '%CX%') AND (Qnotes.Description Not Like '%CARRIER%')
    

    Wednesday, June 28, 2017 11:53 AM

All replies

  • Hi NeuroTransmitter,

    are your data on the sheet or in the database?

    if you are using the database then let us know which database you are using.

    are you using any VBA code to run this query?

    I can see that in the query you are using "Qnotes" as table name.

    but I don't understand why you write it 2 times ?

    let us know how you execute this query so we can try to reproduce the issue and try to find the solution for the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 4, 2017 8:52 AM
    Moderator
  • It is a Microsoft Query. It queries a table in Access called Qnotes.  "Qnotes" actually appears only once in the code otherwise the query can't run.  When I hit "SQL View" it shows up twice.
    Wednesday, July 5, 2017 11:31 AM
  • Hi NeuroTransmitter,

    I try to make a test on my side.

    for that I create one table and query to fetch data from that table in Access.

    Query:

    SELECT avg(salary) from salaryincrease;
    

    Output in Access:

    then I try to make a test with Microsoft Query.

    below is the result in Excel.

    I also try to directly fire query and bring the result in Cell using Access Database.

    by both ways I get result as zero you can see in pic above.

    so I suggest you to first try to execute the simple query without 'where' clause and check the result in Excel.

    if you get correct result in Excel then maybe cause is because of your 'where' clause.

    try to add NZ() function in your query around average to check whether it may solve your issue.

    Nz Function

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 6, 2017 2:12 AM
    Moderator