I have a problem with dividing the value of 0 by 0 in an access database

Question

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)

Posted 9-Jan-23 12:38pm

Islam Awad

Updated 10-Jan-23 3:41am

Add a Solution

Comments

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

Add a Solution

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

CHill60 · Answer 1 · 2023-01-10T03:41:00

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

SQL

SELECT Value1 / Value2)
FROM Table1;

I can use

SQL

SELECT iif(Value2 = 0, 0, Value1 / Value2)
FROM Table1;

The IIF function[^] is essentially the equivalent of using CASE WHEN in T-SQL

I have a problem with dividing the value of 0 by 0 in an access database

1 solution

Solution 1

Add your solution here

Preview 0

I have a problem with dividing the value of 0 by 0 in an access database

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us