Others

How to Fix Access DCount Returning Zero with TempVars

Aamir Naveed Akram Sep 27, 2026 869 views

Question details

The user needs to correctly format an Access DCount expression using TempVars to return accurate record counts instead of zero.

How to Fix Access DCount Returning Zero with TempVars

Product: Microsoft Access
Device & OS: not provided
Scenario: Using DCount with multiple TempVars criteria (Routing Number and SuspendedClub_ID) to count matching database records.
Observed behavior: The DCount expression returns zero even when records matching the specified criteria exist in the database.

Before you start

Verify the exact data types (text vs. numeric) of the fields in your query or table, as this dictates whether you need to use single quotes when formatting the TempVars within the criteria string.

Solution 1Recommended

Concatenate TempVars Values into the Criteria String

Properly parse the TempVars variables outside the literal criteria string using the ampersand (&) operator so Access can evaluate their actual values.

When referencing TempVars inside domain aggregate functions like DCount, placing them entirely inside double quotes causes Access to read the variable name as literal text. By stepping out of the quotes and concatenating the string, Access evaluates the TempVar's value before applying the filter.

Open your Expression or VBA Code

Navigate to the Expression Builder, macro, or VBA module where your DCount function is currently failing.

Modify the DCount Criteria String

Adjust the criteria string to concatenate the TempVars. For numeric fields, use the following syntax: DCount("*", "qry_PT_A_Banks", "[Routing #]=" & TempVars!RoutingNumber & " AND SuspendedClub_ID=" & TempVars!Suspended_ID).

Adjust for Text or Date Fields (If Applicable)

If any of the target fields are Text, wrap the concatenated value in single quotes: "[Text Field]='" & TempVars!TextValue & "'". For Date fields, use octothorpes (#): "[Date Field]=#" & TempVars!DateValue & "#".

Verify with Debug.Print

If writing in VBA, insert Debug.Print DCount(...) before your main code execution to test the output in the Immediate Window and ensure the correct record count is returned.

Concatenate TempVars Values into the Criteria String

Formatting Check: Using standard string concatenation (&) ensures that variables dynamically pass their values into SQL strings and Access domain functions without error.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While WPS Office does not include a direct database replacement for Microsoft Access, it is an exceptional, free alternative for your daily word processing, spreadsheet, and presentation needs. Enjoy seamless compatibility with Microsoft Office formats in a single, lightweight application.

1. Visit the Official Website: Navigate to wps.com to explore the free, all-in-one office suite.
2. Download and Install: Click the free download button and run the lightweight installation file.
3. Edit Your Office Files: Launch WPS Office to instantly open and edit your existing Word, Excel, and PowerPoint files without formatting issues.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Lightweight application that consumes minimal system resources.Familiar user interface requiring zero learning curve.Powerful built-in PDF editing and conversion tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does DCount fail when TempVars are placed inside quotes?

When TempVars are placed entirely inside double quotes, Microsoft Access interprets them as literal text strings (e.g., the word 'TempVars!Value') rather than evaluating their underlying numeric or text values. You must use the ampersand (&) operator to concatenate them into the expression.

How do I format TempVars for date fields in Access DCount?

For date/time fields, you must wrap the TempVars value in octothorpes (hash marks) within the criteria string. For example: "[Date Field]=#" & TempVars!DateValue & "#".

Can I use multiple criteria in a single DCount function?

Yes, you can combine multiple conditions using standard SQL logical operators like AND or OR within the criteria string, provided that all variables are properly concatenated outside the literal string segments.

Office Quick Solutions