Null Value and Calculations

[Roey]

I use the following code to get data from an Access database:

 

       selectSQLOnHand = "Select tblSalesOrderDetails.ItemID, " & _
                               "tblSalesOrderDetails.QtyOrdered, " & _
                               "FROM tblSalesOrderDetails " & _
                               "WHERE tblSalesOrderDetails.LineStatus = 'Closed' " & _
                               "GROUP BY tblSalesOrderDetails.ItemID"

 

In certain cases the value of QtyOrdered = Null, this value is bound to a textbox and used in certain calculations. The Null value is causing a problem in the calculations and needs to be somehow converted to a zero. Any ideas......

[JABE]

selectSQLOnHand = "Select tblSalesOrderDetails.ItemID, " & _

"IIf(IsNull(tblSalesOrderDetails.QtyOrdered), 0, tblSalesOrderDetails.QtyOrdered), " & etc...

[Roey]

Still getting a Null

 

Thanks for your help but I'm still getting a Null value reurned. Not sure if the GROUP BY is causing the problem...

 

       selectSQLOnHand = "Select tblPurchaseOrderDetails.ItemID, " & _
                               "IIf(IsNull(sum(tblPurchaseOrderDetails.POQtyReceived)), 0, sum(tblPurchaseOrderDetails.POQtyReceived)) As OnHand  " & _
                               "FROM tblPurchaseOrderDetails " & _
                               "GROUP BY tblPurchaseOrderDetails.ItemID"

[Roey]

Really Strange Null Issue - Still not solved

 

On the above code if there is a value for the sum(tblPurchaseOrderDetails.POQtyReceived) it will return the True value correctly I tested this by putting

"IIf(IsNull(sum(tblPurchaseOrderDetails.POQtyReceived)), 7777, 9999) As OnHand  "

 

I can return 9999 if there is a value, but it returns Null if there is not a value instead of 7777.......

 

HELP

[Nerseus]

Did you mean to have a SUM() around the QtyOrdered field? You didn't show it in your first post. If you don't, then you don't need the group by.

If you DID want the sum, then try this (you can change it back to a string, but make sure the query runs fine by itself first)

 

Select tblSalesOrderDetails.ItemID, 
IsNull(SUM(tblSalesOrderDetails.QtyOrdered), 0) AS [QtyOrdered]
FROM tblSalesOrderDetails 
WHERE tblSalesOrderDetails.LineStatus = 'Closed' 
GROUP BY tblSalesOrderDetails.ItemID

 

I added an alias on the IsNull(SUM...) otherwise that column won't have a name. I gave it the same name as the column, though you may want to change that.

 

The major change above from the other suggestion is that I didn't use the IIF() -- just the IsNull. IsNull should take two params: If the first is NOT null it returns that value otherwise it returns the second value. If that format isn't supported, try COALESCE

 

-nerseus

[kejpa]

It's a bad idea to use functions in the SQL-statements. Changing the database might ruin your code.

 

I prefer using txtQty.text=MyRow.Item(2).ToString

If the value in the table is null then it will be converted to '', if it's not you'll get the value as a string.

 

Good luck

/Kejpa