need to show "0" for no result query RRS feed

  • Question

  • I'm trying to do a series of counts based on search results but it no records is the result I can't do any future calculations.

    I've tried both Nz(field) and iif((isnull(field)),0, field) with no success where I've had success in the past.

    My goal is to use the results in reports to do calculations on the totpoles.

    My query:

    SELECT [Batches].[Site visit], Count([Pole ID].[ID Pole]) AS totpoles, [Batches].[contract no]

    FROM ([Batches] LEFT JOIN [Packages] ON [Batches].ID = [Packages].[ID Bat]) LEFT JOIN [Pole ID] ON [Packages].[pkg ID] = [Pole ID].[ID Pkg]

    GROUP BY [Batches].[Site visit], [Batches].[contract no], [Packages].[Sent for Internal Review], [Packages].[SENT TO QA/QC FOR APPROVAL], [Packages].[DESIGN APPROVED BY QA/QC], [Packages].[WORKORDER APPROVED BY QA/QC], [Packages].[SEND EJPF TO QA/QC], [Packages].[WO in Ready Status]

    HAVING ((([Batches].[Site visit])=True) AND (([Batches].[contract no])="64432 R26") AND (([Packages].[Sent for Internal Review]) Is Null) AND (([Packages].[SENT TO QA/QC FOR APPROVAL]) Is Null) AND (([Packages].[DESIGN APPROVED BY QA/QC]) Is Null) AND (([Packages].[WORKORDER APPROVED BY QA/QC]) Is Null) AND (([Packages].[SEND EJPF TO QA/QC]) Is Null) AND (([Packages].[WO in Ready Status]) Is Null));

    Tuesday, April 30, 2019 8:28 PM

All replies

  • Hi. Sounds like Nz() should definitely work. Which field is missing data?
    Tuesday, April 30, 2019 8:43 PM