cmd.executenonquery not accepting nz function

[raymyster]

Hello Community,
I have the following query:

SELECT Inventory.Inventory_Id, Inventory.Inventory_description, Inventory.Inventory_stock_count, nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS CurrentlyOut, [Inventory_stock_count]-Nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS NetHere
FROM Inventory;

This query runs perfectly under data access,

But when i put it under vb.net

my query1 = "SELECT Inventory.Inventory_Id, Inventory.Inventory_description, Inventory.Inventory_stock_count, nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS CurrentlyOut, [Inventory_stock_count]-Nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS NetHere
FROM Inventory"


cmd.commadtext = myquery1

cmd.executenonquery()

VB.net compiler do not seem to like it & i see this error

Undefined function 'nz' in expression.

it seems that vb.net is not being able to understand the nz function that replace with 0 . nz replaces null values with 0. i really need nz to be present in the query.

what is a way around this?

Best regards.

[TomW]

I would suggest setting the column to not allow nulls and its default value to zero. Since this is an existing table you might first need to run an update statement to replace nulls with zero before making the table changes.

[TomW]

Taking a closer look at your query I would also add you dont need multiple sub-queries all on the same table.