解决MS Access 2010中IIF语句生成文本框值求和时的#Error问题

Alright, let's figure out why your sum on the FINISHED column is throwing a #Error, even though your IIF statement seems to work correctly at first glance.

First, let's recap the scenario to make sure we're aligned:

• You’re using an IIF statement to populate the FINISHED column: it returns 0 when Cleaning Started is blank, or when its date falls outside the date range set at the top of your form.
• Visually, the output looks correct (like the blue-boxed entries showing 0 for out-of-range dates).
• But when you try to sum the entire FINISHED column, you get nothing but #Error.

Most Likely Cause: Mixed Data Types in the Column #

The biggest culprit here is usually inconsistent data types from your IIF statement. Let me break it down:
If your IIF returns a text string "0" (with quotes) for matching conditions, but returns a numeric value (like a duration or count) for non-matching conditions, your column ends up mixing text and numeric data. Even though "0" looks like a number, the system treats it as text—and summing a column with mixed text/numeric values will trigger a #Error.

Fixes to Try #

1. Ensure Your IIF Returns Numeric 0 (Not Text)

Check your IIF syntax. If you have something like this:

IIF(IsNull([Cleaning Started]) OR [Cleaning Started] < [FormStartDate] OR [Cleaning Started] > [FormEndDate], "0", [YourNumericValue])

Remove the quotes around the 0 so it’s a numeric value instead of text:

IIF(IsNull([Cleaning Started]) OR [Cleaning Started] < [FormStartDate] OR [Cleaning Started] > [FormEndDate], 0, [YourNumericValue])

2. Force Uniform Data Types with Conversion Functions

If the non-matching branch returns a value that’s not explicitly numeric (like a date difference stored as a variant), use a conversion function to lock in a numeric type for both branches. For example:

IIF(IsNull([Cleaning Started]) OR [Cleaning Started] < [FormStartDate] OR [Cleaning Started] > [FormEndDate], CDbl(0), CDbl([YourOriginalValue]))

CDbl() converts values to double-precision numbers, ensuring every entry in the FINISHED column uses the same data type.

3. Check for Hidden Error Values

Sometimes, even if cells show 0, they might hide underlying errors (like #VALUE! from invalid date comparisons). Add an IsError() check to your IIF to catch these:

IIF(IsError([Cleaning Started]) OR IsNull([Cleaning Started]) OR [Cleaning Started] < [FormStartDate] OR [Cleaning Started] > [FormEndDate], 0, [YourOriginalValue])

This ensures any hidden errors are replaced with a valid numeric 0 before summing.

Quick Test to Verify #

To confirm data type issues, try selecting a few cells in the FINISHED column: some might show as "Text" in your tool’s data type indicator (like Access’s status bar), while others show as "Number". Fixing that consistency should resolve the sum error.

内容的提问来源于stack exchange，提问作者NoMiddleName