ACC2000: InputBox Error: Function Isn't Available in Expressions

ID: Q198955

The information in this article applies to:

Microsoft Access 2000

SYMPTOMS

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

When you use the InputBox() function in a query, you may receive the following error message:

Function isn't available in expressions

RESOLUTION

The following four methods enable you to work around this behavior.

Method 1: Use a Parameter

You can use a parameter in the query in place of the InputBox() function. For example, use the parameter:


   [Enter a Date]

Then on the Query menu, click Parameters and type the parameter again, along with its associated data type, in the Query Parameters dialog box.

For more information about parameter queries, click Microsoft Access Help on the Help menu, type "parameter query" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Method 2: Obtain the Information from a Form

If the information that you want to type in the input box can be obtained from a form, you can point the query directly to the form field containing the criteria. For example, use the following expression as a criteria in the criteria line:


   Forms![Form Name]![Control Name]

Method 3: Use the Eval() Function

You can wrap the InputBox() function inside the Eval() function. Use two sets of quotation marks (") or one set of apostrophes (') around each of the text arguments for the InputBox() function. For example, use the expression:


   Eval("InputBox(""Enter a Date"",""Title line"",#1/1/98#)")

-or-

   Eval("InputBox('Enter a Date','Title line',#1/1/98#)")

Method 4: Use the InputBox() Function in a Custom Procedure

Create a custom procedure that contains the InputBox() function, and then use the custom procedure in your query. For example, use the expression


   GetDate()

where GetDate() is the following custom procedure in a module:


Function GetDate()
   GetDate = InputBox("Enter a Date","Title",#1/1/98#)
End Function

For more information about creating custom procedures, click Microsoft Access Help on the Help menu, type "module, create" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Problem

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

Create a new query in Design view based on the Orders table:


   Query: ListOrders
   -----------------------------------------------------
   Type: Select Query

   Field: OrderID
      Table: Orders
   Field: CustomerID
      Table: Orders
   Field: OrderDate
      Table: Orders
      Criteria: InputBox("Enter a Date","Title",#1/1/98#)

On the Query menu, click Run. Note that you receive the following error message:

Click More Help in the error message box and note that you receive the second error message.

REFERENCES

For more information about InputBox() function, click Microsoft Access Help on the Help menu, type "InputBox" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: error

Keywords : kberrmsg QryCrit
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb