Click here to Skip to main content
15,791,891 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When I try to extract the result of Division 2 Field from table in access database If I have a value of 0 an error occurs

What I have tried:

sqlSTR = "SELECT TBL_Category_Item_File.Item_Org_Price2/TBL_Stocks_Balances.Item_QTY AS ['Price']FROM (((TBL_Category_Item_File INNER JOIN TBL_Suppliers_Product ON TBL_Category_Item_File.Item_ID = TBL_Suppliers_Product.Item_ID) INNER JOIN TBL_Suppliers ON TBL_Suppliers_Product.Supp_ID = TBL_Suppliers.Supp_ID) INNER JOIN TBL_Sub_categories ON TBL_Category_Item_File.ID_Sub_categories = TBL_Sub_categories.ID_Sub_categories) INNER JOIN TBL_Stocks_Balances ON (TBL_Stocks_Balances.Item_ID = TBL_Category_Item_File.Item_ID) AND (TBL_Category_Item_File.Item_BarCode = TBL_Stocks_Balances.Item_Barcode)  WHERE  tbl_Category_Item_File.Catg_ID =" & Split(cmblist.Text, " - ")(0)
Updated 10-Jan-23 4:41am
Sandeep Mewara 9-Jan-23 22:06pm    
And thats a valid case, so the query is?
vblover Programmer 10-Jan-23 1:27am    
What's that Error, so.
Richard Deeming 10-Jan-23 3:27am    
Your code is almost certainly vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Aside from that, as the others have said, you haven't told us what the error is.

1 solution

Division by Zero causes an error for everything - Division by zero - Wikipedia[^]

If your data can contain 0 in a column you want to use as a divisor then you need to defensively code for that scenario.

For example if I have a table "Table2" with two numeric columns "Value1" and "Value2" instead of a query
SELECT Value1 / Value2)
FROM Table1;
I can use
SELECT iif(Value2 = 0, 0, Value1 / Value2)
FROM Table1;
The IIF function[^] is essentially the equivalent of using CASE WHEN in T-SQL
Share this answer
Maciej Los 10-Jan-23 10:14am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900