ACC: Cannot Use Intrinsic Constants in Queries, Forms, Reports

• Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use Microsoft Access intrinsic constants in expressions in a query, on a form or report, or in the Condition column of a macro, you receive an error or you are prompted to enter a parameter.

For example, when you use the StrConv() function in the Update To cell of an update query and then run it, you receive the following error message:

   Microsoft Access can't update all the records in the update query.

   Data type mismatch in criteria expression.

If you use the StrConv() function in the ControlSource property of a control on a form, the control displays #Name? in Form view.

If you use the StrConv() function in the Condition column of a macro, you receive the following error message:

   Microsoft Access can't find the name '<intrinsic constant name>' you
   entered in the expression.

CAUSE

The query does not recognize the value of the Visual Basic intrinsic constants you used in the function.

RESOLUTION

To work around this behavior, use one of the following methods:

• Use the numeric value for the conversion argument instead of the constant. For example, instead of using

        Expr1: StrConv([CompanyName],vbProperCase)
  

  use

        Expr1: StrConv([CompanyName],3)
  

  For a list of numeric values associated with each of the intrinsic constants used in the conversion argument of the StrConv() function, search the Help Index for "StrConv function."

  For a list of the numeric values associated with intrinsic constants in other built-in functions in Microsoft Access, search the Help Index for that function.

• Create a procedure that contains the built-in function with its intrinsic constant, and then use that procedure in your query. For example, you can create the following procedure in a module:

        Function UseConstants(FieldName As String)
           UseConstants = StrConv(FieldName, vbUpperCase)
        End Function
  
     Then you can use the custom procedure in your query expression:
  
        Expr1: UseConstants([CompanyName])
  
  

STATUS

This behavior is by design. Intrinsic constants can only be used in Visual Basic for Applications code.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Create the following new query in Design view based on the Customers table:

         Query: qryCustomers
         ---------------------------------------------------------
         Type: Select Query
   

         Field: CompanyName
            Table: Customers
            Criteria: StrConv([CustomerID], vbLowerCase) = "alfki"
   
   

3. On the Query menu, click Run. Note that you receive the error "Data type mismatch in criteria expression." Click OK.

4. Delete the Criteria expression and type the following expression in a new column in the QBE grid:

         StrConv([CompanyName], vbUpperCase)
   

5. On the Query menu, click Run. Note that you are prompted to enter a parameter value for vbUpperCase.

   NOTE: If you type the correct numeric value for the vbUpperCase constant (which is 1) in the Enter Parameter Value dialog box, and then click OK, the query runs correctly.

6. Switch the query back to Design view.

7. On the Query menu, click Update Query (or Update in version 7.0).

8. In the QBE grid, delete the column with the StrConv expressions, and set the Update To cell of the CompanyName column to the following expression:

         StrConv([CompanyName], vbUpperCase)
   

9. On the Query menu, click Run. Click Yes to confirm that you want to update the records, and note that you receive the error "Microsoft Access can't update all the records in the update query." Click No to stop the update.

REFERENCES

For more information about Microsoft Access intrinsic constants, search the Help Index for "constants, intrinsic."

For more information about using the StrConv() function in Microsoft Access, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q110391
   TITLE     : ACC: Sample Function to Capitalize Words and Phrases