Runtime Error 94 in VBA: “Invalid use of Null,” explained and fixed

Error 94 means a Null — the database world's “no value at all” — flowed somewhere Nulls can't go: into a String variable, a comparison, or arithmetic. It lives mostly in Access/VBA and database-driven Excel code, because Null is born in tables: an empty field in a record is Null, and Null contaminates almost everything it touches.

How Null travels

Null is not zero and not an empty string — it's the absence of a value, and it's contagious: Null + 5 is Null, "x" & Null behaves differently from "x" + Null (which is Null), and a comparison with Null yields Null, not True or False. Code that sails through full records crashes on the first sparse one — which is why 94 appears “randomly” on real data.

The usual causes, most common first

Assigning a recordset field directly to a typed variable — Dim s As String: s = rs!Notes explodes the first time Notes is empty.
Arithmetic on optional numeric fields (discounts, adjustments) that are Null rather than 0.
Passing a field into a function that requires a value — string functions, formatting, date math.
Comparisons in conditions where the Null branch silently evaluates to neither True nor False, confusing logic downstream (and sometimes surfacing as 94 later).

The fix

Convert at the boundary with Nz(): s = Nz(rs!Notes, "") and amt = Nz(rs!Discount, 0). Every field crossing from a recordset into typed variables gets an Nz with the sensible default. In pure VBA (outside Access), write the equivalent: If Not IsNull(v) Then ....
Declare receiving variables as Variant when Null is a legitimate state you intend to handle yourself — Variant is the only common type that can hold Null.
Test with IsNull(), never with = Null — comparison against Null never returns True, even for Null itself.
Push defaults into the query for reporting code: SELECT Nz(Notes,'') ... (or COALESCE in SQL Server) so downstream code never meets Null at all.

Do not download anything for this error. It's a data-shape issue inside the macro: some records have empty fields and the code assumed they never would. Nz at the boundary is the entire remedy.

If it still happens

Find which field: on the failing line, Immediate-window each candidate with ? IsNull(rs!FieldName), or log the record's key before processing. Then decide policy per field — some Nulls mean “use the default,” others mean “this record is incomplete; skip and log it.” That distinction is business logic, and error 94 is often the first honest signal that it was never decided.

Common questions

Is Null the same as an empty string or zero? No — and treating them as equal is the root of this error family. Empty string is text of length zero; 0 is a number; Null is the absence of any value, and it propagates through expressions in ways the others don't.

Why does it crash on old data but not new? Newer records get filled in completely by current forms/processes; older or imported records carry empty fields. The code was always vulnerable — the sparse record just arrived.