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

• Microsoft Access 97

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

   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")

   [Enter a Date]

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#)

   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")

   Forms![Form Name]![Control Name]

   ARTICLE-ID: Q95931
   TITLE     : 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#)

   GetDate()

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

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