Others

How to Fix Division by Zero or Null Errors in Access, Crystal Reports, and VB6

WPS Editor Sep 30, 2026 870 views

Question details

The user needs to resolve an issue where an Access query executes successfully in Microsoft Access but returns a 'division by zero (null)' error when accessed through Crystal Reports or VB6.

Product: Microsoft Access, Crystal Reports, VB6
Device & OS: not provided
Scenario: Executing a Microsoft Access query containing calculated aliases or division operations from an external application using a database driver.
Observed behavior: The query works without errors natively in Access, but fails with division by zero or null errors in Crystal Reports or VB6 because the driver evaluates expressions differently.

Before you start

Before proceeding, backup your Access database (.accdb or .mdb) and write down the exact SQL query or Crystal Reports formula that is triggering the error.

Solution 1Recommended

Refactor Calculated Aliases in the SELECT Clause

Restructure your SQL query to avoid using a freshly defined alias in subsequent calculations within the same clause, as external drivers cannot parse this correctly.

When an Access query is executed through Crystal Reports or VB6 via an ODBC/OLE DB driver, the driver's engine evaluates expressions more rigidly than the native Access engine. If you create an alias (e.g., AS Total) and then try to divide by that alias in the same SELECT statement, the external driver often evaluates it as null, leading to a division by zero error.

Open the Query in SQL View

Launch Microsoft Access, right-click the problematic query in the navigation pane, and select 'Design View'. Then, click the 'View' button on the ribbon and choose 'SQL View'.

Identify Alias Usage

Look for any fields where an alias is created using the 'AS' keyword (e.g., [Field1] + [Field2] AS MySum) and check if 'MySum' is used again in the same SELECT clause.

Repeat the Original Expression

Replace the reference to the alias with the full original expression. For example, instead of writing '100 / MySum AS Average', write '100 / ([Field1] + [Field2]) AS Average'.

Save and Test

Save the query in Access, then run your report in Crystal Reports or execute the VB6 application to verify the division by zero error is resolved.

Refactor Calculated Aliases in the SELECT Clause

Alternative Method: You can also save the query defining the aliases as a subquery, and then create a parent query that performs the division using the subquery's fields. The driver processes nested queries reliably.

Solution 2

Add Null and Zero Checks to Divisors

Ensure that no field used as a divisor or MOD operand evaluates to zero or null when processed by the external driver.

Solution 3

Validate Formulas within Crystal Reports

Isolate the issue by checking if the division error originates from the report's formula engine rather than the database query.

Free Microsoft Office alternative

Enhance Your Workflow with WPS Office

While troubleshooting database driver errors in Access and VB6, consider streamlining your broader document tasks. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office, perfect for reviewing exported database reports in spreadsheets, docs, and PDFs.

1. Download the Installer: Visit the official WPS website and download the free WPS Office installation package for your operating system.
2. Install WPS Office: Run the installer, follow the on-screen prompts, and launch the application.
3. Open Your Reports: Use WPS Spreadsheets or WPS Writer to open, edit, and format reports exported from Crystal Reports or VB6.

Seamless format compatibility with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx).Lightweight software architecture ensuring rapid installation and lightning-fast startup times.Familiar tabbed user interface, meaning a zero learning curve when migrating from traditional office suites.Built-in PDF editing tools ideal for managing and annotating exported Crystal Reports.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my query work natively in Access but fail in VB6 or Crystal Reports?

Microsoft Access uses its native Jet/ACE database engine, which can handle calculated aliases gracefully and may ignore specific null errors on execution. VB6 and Crystal Reports use external ODBC or OLE DB drivers that parse SQL queries strictly; they evaluate expressions differently and will throw an error if an alias is reused in the same SELECT statement or if a division by zero occurs.

How do I safely handle null values in Access queries to avoid division errors?

You can wrap your mathematical divisors in an IIf() statement combined with the IsNull() function. For instance, using IIf(IsNull([MyField]) Or [MyField]=0, 1, [MyField]) forces the engine to divide by 1 instead of 0 or null, preventing the driver from failing.

Can using subqueries fix the division by zero error?

Yes. By separating your calculations across multiple queries, you prevent the driver from evaluating a calculation and referencing its alias simultaneously. Place the base calculations in a nested subquery, and perform the final division in the parent query.

Does the database driver version affect how VB6 executes the query?

Absolutely. Different versions of the Microsoft Access ODBC driver or OLE DB providers have varying levels of strictness when parsing nested expressions and handling null data. Upgrading or changing your driver can sometimes expose expression problems that were previously ignored.

Office Quick Solutions