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

ID: Q209903

The information in this article applies to:

Microsoft Access 2000

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

SYMPTOMS

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

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.

When you use the StrConv() function in the Criteria cell of a select query, you receive the following error message:

Data type mismatch in criteria expression.

If you use the StrConv() function in the ControlSource property of a control on a report, Microsoft Access prompts you for a parameter value.

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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

CAUSE

The query does not recognize the value of the Visual Basic intrinsic constants that 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)

Expr1: StrConv([CompanyName],3)

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

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

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"

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

Delete the Criteria expression and type the following expression in a new column in the QBE grid:
StrConv([CompanyName], vbUpperCase)

On the Query menu, click Run. Note that you receive the error mentioned earlier.

Switch the query back to Design view.

On the Query menu, click Update Query.

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)

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 a list of numeric values associated with each of the intrinsic constants used in the conversion argument of the StrConv() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "StrConv()() function" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: action update delete make table select

Keywords : kberrmsg kbusage AccCon QryOthr KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb