Flaws in Microsoft Access

Provided by Allen Browne, July 2006.  Last updated: April 2010.

Parameter of type Text is evaluated wrongly

If you declare a query parameter of type Text, JET may get it wrong. To work around this problem, we suggest you declare other types (Short, Long, Date, Boolean, etc) but do not explicitly declare parameters on Text type fields.

What is a parameter?

In the query design window, if you type a name that Access cannot resolve as a field name, it treats that name as a parameter -- a value you will supply when you run the query. If the name is something like [Forms].[Form1].[Text0], the Expression Service tries to read the value from the form. If that doesn't work (doesn't match, or the form is not open), Access pops up a parameter dialog asking you to type in the value:

Why declare parameters?

Especially in JET 4 (Access 2000 and later), Access is likely to misunderstand the entry if the field is a number or date.

You can prevent the misunderstanding on the form and in the query:

1. Set the Format property of the unbound text box, so the form understands the data type, as described in the article, Calculated fields misinterpreted.

2. Declare the parameter so the query understands the data type.
   In Access 1 - 2003, choose Parameters on the Query menu in query design.
   In Access 2007 and later, you find Parameters on the Show/Hide group of Design tab of the ribbon.
   Then enter your parameter name and data type in this dialog:

How does Access get Text parameters wrong?

When you declare a parameter of type Text, JET calls the Expression Service (ES) which tries to get the value from the control on the form. If the form is open and has the named control, the ES reads the value from the form and passes it back to JET. The ES makes a mistake when the control is Null (nothing in the text box), as explained below. If the ES cannot resolve the name from the open form, Access pops up a dialog asking you to enter the parameter value. If you leave this dialog blank, JET correctly understands it as Null.

The two queries below use identical logic. Query1 applies criteria to a Number field, so declares a numeric parameter. It works fine, returning all records when the text box is blank. Query2 applies criteria to a Text field, so declares a Text parameter. The results are wrong: no records are returned when the text box is left blank.

See for yourself:

1. Open the Northwind sample database. (Recent versions of Northwind may not be the same as discussed here.)
2. Create a new form (Design View), and add text boxes named txtEmployeeID and txtCustomerID. Save the form with the name Form1.
3. Open the form in Form view. Do not enter anything in either text box.
4. Create a new query, switch to SQL View (View menu), and paste in the text from Query1 above.
5. Create another query, and paste the text from Query2 above into SQL View.
6. Test the queries.

Why does it fail?

As explained above, the ES reads the value from the text box, and passes it to the query. For a parameter declared as type Text, the ES misinterprets the Null as being a zero-length string. This is a very basic mistake that you expect first year database students to make. [1]

Once you know the flaw exists, you can work around it by NOT explicitly declaring parameters of type Text. If you don't declare the parameter, the query works correctly.

To demonstrate the workaround, remove the first line from Query2, so that it starts with SELECT.

Conclusion

For numeric and date fields, do declare parameters. This will prevent problems where JET misunderstands the data type.

For Text fields, do not declare parameters. Text is the default type anyway, and not declaring the parameter works around this bug.

Footnote:

[1] DLookup() has the opposite bug, mistaking a zero-length string for Null. For a workaround for that bug, see: ELookup.
     For details on how to avoid making these mistakes yourself, see Common errors with Null.