Microsoft KB Archive/275116

Knowledge Base

ACC2002: A Parameter Prompt for a Memo Field Permits Only 255 Characters

Article ID: 275116

Article Last Modified on 6/7/2004

---

APPLIES TO

• Microsoft Access 2002 Standard Edition

---

This article was previously published under Q275116

Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you type data into a parameter prompt for a Memo field, you are limited to 255 characters.

CAUSE

The character limit for a parameter prompt is 255 characters, regardless of the data type defined.

RESOLUTION

When You Are Using a Query in a Microsoft Access Database (MDB) or a Stored Procedure in a Microsoft Access Project (ADP):

You can use the Query by Form technique to specify the criteria for a query.

For additional information about how to use the Query by Form technique in a Microsoft Access database, click the article number below to view the article in the Microsoft Knowledge Base:

304428 How to Use the Query by Form (QBF) Technique

For additional information about how to use the Query by Form technique in a Microsoft Access Project, click the following article number to view the article in the Microsoft Knowledge Base:

286828 How to implement Query By Form (QBF) in an Access project

When You Are Using an Inline Function in a Microsoft Access Project (ADP):

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

1. Open the NorthwindCS sample project, and then create the following inline function:

   SELECT CategoryID, CategoryName, Description
   FROM dbo.Categories
   WHERE (Description LIKE @Enter_Description)
                       

2. Save the function as fnTest, and then close it.
3. Create a form that is based on the function fnTest.

4. Type the following in the InputParameters property of the form:

   @Enter_Description=Forms!frmParameter!Text0
                       

5. Save the form as frmTest, and then close it.

6. Create an unbound form. Add a text box to the form, and then set the following properties:

   Name:  Text0
   Caption:  Text0
                       

7. Add a command button to the form, and then set the following properties:

   Name:  OpenForm
   Caption:  Open Form
                       

8. Set the OnClick property of the command button to the following event procedure:

   DoCmd.OpenForm "frmTest"
                       

9. Save the form as frmParameter.
10. Open frmParameter in Form view, type Cheeses into Text0, and then click the Open Form button.
    
    Notice that you can type more than 255 characters in Text0, and that the form displays records that match the specified criteria.

MORE INFORMATION

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the Northwind sample database.
2. In the Database window, right-click the Categories table, and then click Copy.
3. Right-click anywhere in the Database window, and then click Paste.
4. In the Paste Table As dialog box, type tblMemo in the Table Name box, and then click OK.
5. Create a new query. In the Show Table dialog box, click Close.
6. On the View menu, click SQL View.

7. Paste the following code into the SQL View window:

        PARAMETERS MyParameter Text;
        UPDATE tblMemo SET tblMemo.Description = [MyParameter];

8. Save the query, and then run it.
   
   Notice that the parameter prompt is limited to 255 characters.

Additional query words: pra prb

Keywords: kbprb KB275116

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.