Why Your Access Reports Look Wrong After Moving to SQL Server

Your migration went smoothly. The data is in SQL Server. The forms work. Then someone runs a monthly report and the numbers are different from last month’s Access version. Not wildly different — maybe off by a few percent, or a few records are missing, or the subtotals do not match.

This is one of the most stressful post-migration problems because reports often feed into financial decisions, compliance requirements, or executive dashboards. “The numbers changed” is not an acceptable answer.

Here is why it happens and how to fix it.

Cause 1: Sort Order Differences

What you see: Records appear in a different order, or grouping breaks down and subtotals are wrong.

Why it happens: Access and SQL Server use different default sort orders (collations). Access sorts based on the Windows locale settings. SQL Server sorts based on the database’s collation setting.

The difference is usually subtle — it affects how uppercase/lowercase letters sort, how accented characters sort, and how special characters sort. But if your report groups records by a text field and the sort order changes, records can end up in different groups.

Fix: Check the SQL Server database collation. For most Access migrations, SQL_Latin1_General_CP1_CI_AS (case-insensitive, accent-sensitive) matches Access behavior most closely. If your report relies on a specific sort order, add explicit ORDER BY clauses to the report’s record source rather than relying on default sorting.

Cause 2: NULL Handling Differences

What you see: Counts, sums, or averages produce different results. Records that appeared in the Access report are missing from the SQL Server report, or extra records appear.

Why it happens: Access and SQL Server handle NULL values differently in several contexts:

Comparisons. In Access, "" = "" is True and Null = Null is… also treated as True in some contexts (like query criteria). In SQL Server, NULL = NULL is always False. A WHERE clause like WHERE Notes = NULL returns zero rows in SQL Server but may have returned rows in Access.
Aggregations. Both databases ignore NULLs in SUM, AVG, and COUNT(column). But COUNT(*) counts all rows including NULLs. If your report uses COUNT(column) and the column now has NULLs where Access had empty strings, the count changes.
Concatenation. In Access, "Hello" & Null produces "Hello". In SQL Server, "Hello" + NULL produces NULL. If a report concatenates fields and one is NULL, the entire result becomes NULL in SQL Server.

Fix: - Replace WHERE column = NULL with WHERE column IS NULL - Replace WHERE column <> NULL with WHERE column IS NOT NULL - Use ISNULL(column, '') or COALESCE(column, '') in SQL Server to handle NULLs in concatenation - Review whether empty strings in Access should be NULLs or empty strings in SQL Server, and be consistent

Cause 3: Date/Time Precision Differences

What you see: Reports that filter by date include or exclude records at the boundary. For example, a report for “June 2026” includes a few records from July 1, or misses records from June 30.

Why it happens: Access Date/Time fields store dates with time precision to the second. SQL Server’s DATETIME rounds to the nearest 3.33 milliseconds, and DATETIME2 stores up to 100-nanosecond precision.

If your Access records had times like 6/30/2026 11:59:59 PM and your report filters for WHERE OrderDate <= #6/30/2026#, Access interprets the boundary as midnight at the start of June 30. SQL Server may interpret it differently depending on how the comparison is structured.

Fix: Be explicit about date boundaries in your report queries: - Instead of WHERE OrderDate <= '2026-06-30', use WHERE OrderDate < '2026-07-01' - This avoids ambiguity about whether the boundary time is midnight at the start or end of the day

Cause 4: Floating-Point Rounding

What you see: Totals are off by pennies. A sum that was $10,432.50 in Access is now $10,432.49 or $10,432.51 in SQL Server.

Why it happens: If your Access database stores monetary values in Number (Double) fields instead of Currency fields, the floating-point representation can differ between Access and SQL Server. Floating-point numbers cannot represent all decimal values exactly, and the rounding behavior differs between implementations.

Fix: - Use DECIMAL(19,4) or MONEY in SQL Server for monetary values, not FLOAT - If the Access source used Double, the migration tool may have created a FLOAT column. Change it to DECIMAL. - Use ROUND() in your report queries to enforce consistent rounding

Cause 5: Query Syntax Translation Errors

What you see: The report returns fewer or more records than expected, or calculated fields show different values.

Why it happens: The report’s record source query may contain Access-specific functions that either failed silently or translated incorrectly during migration.

Common culprits: - IIf(condition, trueValue, falseValue) — may not translate correctly if the condition involves NULLs - Nz(field, default) — not a SQL Server function; needs to be ISNULL(field, default) - Format(dateField, "yyyy-mm") — Access Format function differs from SQL Server’s FORMAT - String concatenation with & — needs to be + in SQL Server (but with NULL handling differences) - Like "*pattern*" — wildcards need to be % in SQL Server

Fix: Open the report in Design View, examine the Record Source property, and check every function call and operator against the target database’s syntax. Test the query directly in the database management tool (SSMS for SQL Server) to verify it returns the expected results.

Cause 6: Missing or Different Indexes

What you see: The report runs but takes much longer, and when users get impatient and cancel it partway through, the results are incomplete.

Why it happens: Migration tools create primary key indexes but may not recreate all the secondary indexes from the Access database. Without proper indexes, the report query does a full table scan instead of an index lookup. On a large table, this is the difference between a 2-second report and a 2-minute report.

Fix: Check which indexes exist on the server tables. Create indexes on columns used in the report’s WHERE, JOIN, GROUP BY, and ORDER BY clauses. Use the database’s query plan analyzer (SSMS’s “Include Actual Execution Plan” for SQL Server) to identify missing indexes.

Cause 7: Record Source Pulls All Data

What you see: The report opens but hangs for a long time before displaying anything, or displays incomplete data.

Why it happens: In Access, querying a local table is fast even without filters. After migration, the same unfiltered query pulls all data across the ODBC connection. A report that scanned 100,000 records locally in 2 seconds now transfers 100,000 records over ODBC in 45 seconds.

If the report has subreports, each subreport executes its own query for every group in the main report. A report with 50 groups and 3 subreports executes 150 queries.

Fix: Add parameters to the report so users specify date ranges, categories, or other filters. Convert the record source to a pass-through query that executes on the server. For subreports, consider replacing them with a single query that joins all needed data.

The Testing Process

After migration, systematically test every report:

Run the report in Access against the original (pre-migration) database
Run the same report in Access against the migrated (SQL Server) database
Compare the results row by row for at least one representative data set
Check totals, subtotals, and record counts
Test date boundaries (first and last day of month, year)
Test with NULL data, empty data, and edge cases

Do this for every report. Do not assume that if one report works, they all do. Each report has its own record source with its own potential issues.