SQL Query returns blank instead of zero

[johnisotank]

Hi all,

I have a SQL querytable which imports nicely into my spreadsheet and I have a SELECT statement which groups the work done on a table and outputs like this..

Job Type, Number of Jobs, Total Sales Value
Car Wash, 10, £50.00
Bike Wash, 20, £75.00
Moped Wash,

As you can see, the moped wash brings in blank cells instead of zero values, is there anyway I can get this to pull in zeros please..

John

 

[PHV]

What is your actual SQL code ?
Anyway, why not simply format the cells ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NWBeaver]

Is your spreadsheet setup to display zero values?
Excel, for example, has a option not to display zero values.

 

[johnisotank]

Hi, thanks for replies.

NWBeaver: My sheet is set up to display zero values (I thought that may be a problem so checked it earlier).

PHV: Code is below(apologies for the length of it but I didn't want to shortn, what would I format the cells as (I don't know of any format that says show zero values)?

  sSQL = "SELECT a.Type AS 'Type Of Job', COUNT(DISTINCT a.JobNo) AS 'Number Of Jobs', SUM(b.Quantity * b.Price) AS 'Total Sales Value'"
sSQL = sSQL & vbLf
sSQL = sSQL & "FROM tblLift a LEFT OUTER JOIN "
sSQL = sSQL & "tblJobMethods b ON a.JobNo = b.JobNo"
sSQL = sSQL & vbLf
sSQL = sSQL & "WHERE (DateIn >= '" & DateFrom & "' AND DateIn <= '" & DateTo & "')" & CustCode & JobsFor
sSQL = sSQL & vbLf
sSQL = sSQL & "GROUP BY a.Type"
    
Rows("10:65536").ClearContents
    
Dim qt As QueryTable
     connstring = GetServer
     With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A10"), Sql:=sSQL)
         .Name = "Job_Report_Summary"
                 .Refresh
            End With

Thanks
John

 

[Andrzejek]

This part of your SQL:

SUM(b.Quantity * b.Price) AS 'Total Sales Value'"

(probably) returns NULL which transalets to blank in Excel.

In ORACLE I would use NVL:

[blue]NVL([/blue]SUM(b.Quantity * b.Price) AS 'Total Sales Value'"[blue], '0')[/blue]

What's the value of tblJobMethods.Quantity and tblJobMethods.Price for Moped Wash?

Have fun.

---- Andy

 

[Andrzejek]

Ooops, NVL statement should look like:

NVL(SUM(b.Quantity * b.Price), '0') AS 'Total Sales Value'

Have fun.

---- Andy

 

[johnisotank]

Hi Andrzejek,

you got me pointed in the right direction. The equivalent for NVL was ISNULL.

so my code looks like this and now works perfect..

ISNULL(SUM(b.Quantity * b.Price), '0') AS 'Total Sales Value',0)

thanks very much for your help

John

 

[Andrzejek]

Glad to help :->

Have fun.

---- Andy