时间戳

首页 > 解决方案 > 空记录集的Excel VBA SQL SUM不返回零

sql - 空记录集的Excel VBA SQL SUM不返回零

问题描述

我想使用一个返回列总和的子查询，例如：

3 + SELECT SUM(field) FROM some-table WHERE some-criteria

当条件结果为 0 条记录时，子查询不返回 0，因此整个查询失败并且不返回任何内容（长度为零的内容）。

当有记录时，一切都很好。

我试过SUM(IIF(field IS NULL, 0 , field) FROM...了，但 ti 并没有改变行为。

这是与较大查询隔离的子查询：

strSQL = "SELECT SUM(IIF(RCTPAY2.[Sum Paid] IS NULL ,0 , RCTPAY2.[Sum Paid])) FROM [Receivables$B10:K32] AS RCTPAY2 WHERE RCTPAY2.[Invoice Number]=500009 AND RCTPAY2.[Method]='Tax Deduct'"

我怎样才能让它总是返回一个有效的数字（如果没有记录，则返回 0，如果有记录，则返回字段的 SUM）？

这是我正在使用的提供者：

strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"

编辑：根据 JNevill 的要求，我在这里复制完整的 SQL 查询。寻找HDR.[Total NIS]-(SELECT SUM：

SELECT 'C100'+'000000000'+'024227357' + '320' + 
       FORMAT(HDR.[Invoice Number],'00000000000000000000') + 
       IIF(ISNULL(HDR.[Print DateTime]),'            ', 
           FORMAT(HDR.[Print DateTime],'YYYYMMDDHHMM')), 
           LEFT(TRIM(HDR.[Customer Name])+'                                                  ',50),  
       IIF(ISNULL(CST.[Bill To: Address1]), '                                                  ',  
           LEFT(TRIM(CST.[Bill To: Address1])+'                                                  ',50)),  
       IIF(ISNULL(CST.[Bill To: Address4]), '          ',  
           LEFT(TRIM(CST.[Bill To: Address4])+'          ',10)),
       IIF(ISNULL(CST.[Bill To: Address2]), 
           '                              ',  
           LEFT(TRIM(CST.[Bill To: Address2])+'                              ',30)),  
       IIF(ISNULL(CST.[Bill To: Address3]), '        ',  
           LEFT(TRIM(CST.[Bill To: Address3])+'        ',8)), 
       IIF(ISNULL(CNTRY.[Country]), 
           '                              ',  
           LEFT(TRIM(CNTRY.[Country])+'                              ',30)),
       IIF(ISNULL(CST.[Bill To: Address5]), '  ',  CST.[Bill To: Address5]), 
       IIF(ISNULL(CST.[Phone]), '               ',  
           LEFT(TRIM(CST.[Phone])+'               ',15)) ,  
       IIF(ISNULL(CST.[Company ID]), '000000000',  
           FORMAT(CST.[Company ID],'000000000')) + 
           (SELECT FORMAT(RCTPAY.[Date],'YYYYMMDD') 
            FROM [ùåøåú ú÷áåìéí$B10:K32] AS RCTPAY 
            WHERE RCTPAY.[Invoice Number]=HDR.[Invoice Number] 
              AND RCTPAY.[Line Number]=1) +  
                  IIF(True, 
                      IIF(HDR.[Currency]<>'ù÷ì çãù', 
                          FORMAT(100*HDR.[Total], IIF(HDR.[Total]<0, 
                                                      '00000000000000',
                                                      '+00000000000000')), 
                      '               '), '               ') +  
                  IIF (True, 
                       IIF(HDR.[Currency]<>'ù÷ì çãù', 
                           CUR.[IRS Code], '   '), 
                       '   ') + 
                  FORMAT(100*(HDR.[Subtotal] * HDR.[Exchange Rate]),
                  IIF(HDR.[Subtotal]<0,'00000000000000','+00000000000000')) +
                  IIF(ISNULL(HDR.[Discount%]),'               ', 
                      FORMAT(ABS(100*(HDR.[Subtotal] * HDR.[Discount%])),
                  IIF(HDR.[Subtotal]<0,'+000000000000000',
                      '-00000000000000'))) + 
                  FORMAT(100*(HDR.[Total In Currency] * HDR.[Exchange Rate]),
                         IIF(HDR.[Total In Currency]<0,
                             '00000000000000','+00000000000000')) + 
                  FORMAT(100*(HDR.[VAT] * HDR.[Exchange Rate]),
                         IIF(HDR.[VAT]<0,'00000000000000','+00000000000000')) + 
                  FORMAT(100*(HDR.[Total NIS]-
                         (SELECT SUM(RCTPAY2.[Sum Paid]) 
                          FROM [ùåøåú ú÷áåìéí$B10:K32] AS RCTPAY2 
                          WHERE RCTPAY2.[Invoice Number]=HDR.[Invoice Number] 
                            AND RCTPAY2.[Method]='ðéëåé îñ áî÷åø')),
                         IIF(HDR.[Total NIS]<0,
                             '00000000000000','+00000000000000')) + 
                  FORMAT(100*(SELECT SUM(RCTPAY3.[Sum Paid]) 
                              FROM [ùåøåú ú÷áåìéí$B10:K32] AS RCTPAY3 
                              WHERE RCTPAY3.[Invoice Number]=HDR.[Invoice Number] 
                                AND RCTPAY3.[Method]='ðéëåé îñ áî÷åø'),
                        '+00000000000') +  
                  FORMAT(HDR.[Customer Number],'000000000000000') +
                         IIF(ISNULL(HDR.[Source Invoice]), '          ', 
                             RIGHT('          ' + 
                             FORMAT(HDR.[Source Invoice],'##########'), 10)) +  
                         IIF(HDR.[Status]='îáåèìú','1',' ') +  
                             FORMAT(HDR.[Date],'YYYYMMDD') + '       ', 
                                   '         ',  FORMAT(HDR.[Invoice Number],
                                                        '0000000') + '             ' 

FROM ((([çùáåðéåú å÷áìåú$B12:AB55] as HDR 
LEFT JOIN [ì÷åçåú$B12:X20] as CST 
       ON HDR.[Customer Number] = CST.[Customer Number]) 
LEFT JOIN [èáìàåú$ET12:EU254] as CNTRY 
       ON CST.[Bill To: Address5] = CNTRY.[Code]) 
LEFT JOIN [èáìàåú$K10:O25] as CUR 
       ON HDR.[Currency] = CUR.[Currency]) 
WHERE HDR.[Date]>=43101 
  AND HDR.[Date]<=43465 
  AND HDR.[Status]<>'îîúéðä' 
  AND HDR.[Invoice Type]='çùáåðéú îñ/÷áìä'

标签： sqlexcelvba

解决方案

这是您的查询：

SELECT SUM(IIF(RCTPAY2.[Sum Paid] IS NULL ,0 , RCTPAY2.[Sum Paid]))
FROM [Receivables$B10:K32] AS RCTPAY2
WHERE RCTPAY2.[Invoice Number] = 500009 AND RCTPAY2.[Method] = 'Tax Deduct';

你在想：“天哪，这不应该返回NULL，因为我在IIF()“。但是你遗漏了一些东西：如果没有行怎么办？这就是你遇到的问题。

因此，不要IIF() 在. _ _ 另外，使用而不是. 所以：SUM()SUM()NZ()IIF()

SELECT NZ(SUM(RCTPAY2.[Sum Paid]), 0)
FROM [Receivables$B10:K32] AS RCTPAY2
WHERE RCTPAY2.[Invoice Number] = 500009 AND RCTPAY2.[Method] = 'Tax Deduct';

SUM()无论如何都会忽略NULL值，因此无需将它们变为零。

如果NZ()不起作用，请尝试 ANSI 标准COALESCE()函数：

SELECT COALESCE(SUM(RCTPAY2.[Sum Paid]), 0)
FROM [Receivables$B10:K32] AS RCTPAY2
WHERE RCTPAY2.[Invoice Number] = 500009 AND RCTPAY2.[Method] = 'Tax Deduct';

我只推荐IIF()或IFNULL()（和NZ()）出于绝望——这些是特定于数据库的定制功能。

推荐阅读