ACC97: InputBox Error: "Function Isn't Available in Expressions"

ID: Q164239

The information in this article applies to:

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

When you click More Help in the error message box, you also receive the following error
The topic does not exist. Contact your application vendor for an updated Help file. (129)


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, instead of the expression 

   InputBox("Enter a Date")

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, search the Help Index for "parameter queries."

Method 2: 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, instead of the expression 

   InputBox("Enter a Date","Title",#1/1/95#)

use the expression: 

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


   -or- 


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

Method 3: Use the Query by Form Technique

If the information you want to type in the input box can be obtained from a form, you can use the query by form technique to supply the criteria. For example, instead of the expression 

   InputBox("Enter a Date")

use the expression: 

   Forms![Form Name]![Control Name]

For more information about using the query by form technique, search the Help Index for "query by form," and see the following article in the Microsoft Knowledge Base:

Q95931 ACC: How to Use the Query-by-Form (QBF) Technique

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, instead of the expression 

   InputBox("Enter a Date","Title",#1/1/95#)

use the expression: 

   GetDate()

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

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

For more information about creating a custom procedure, please see the Microsoft Access manual, "Building Applications with Microsoft Access 97," Chapter 2, "Introducing Visual Basic," pages 57-81.


STATUS

This behavior is by design.


MORE INFORMATION

Steps to Reproduce Behavior


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


  2. 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/95#)


  3. On the Query menu, click Run. Note that you receive the error message. Click More Help in the error message box and note that you receive the second error message.



REFERENCES

For more information about the InputBox() function, search the Help Index for "InputBox function," or ask the Microsoft Access 97 Office Assistant. 


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

Last Reviewed: April 17, 1999