What is a text box that gets its value from a field in a table?

This MSAccess tutorial explains how to create a query where the LIKE clause gets its condition from a text box in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, how can I set up a query so that the LIKE clause gets its condition from a text box on my form?

Answer: We'll demonstrate how to do this with the example below.

In this example, we have a form called frmCustomers that has a text box called SearchValue.

We've then set up a query that uses a LIKE condition whose condition comes from the SearchValue field on the frmCustomers form. As indicated by:

Like [forms]![frmCustomers]![SearchValue] & "*"

This query will return all records from the Customers table where the ContactTitle field starts with the value entered in the SearchValue field.

So if "Manager of " was entered in the SearchValue field. The query would return all records from the Customers table where ContactTitle begins with "Manager of ".

A string is text made of a readable letter, a symbol, a word or a group of words.Microsoft Access supports two broad types of text actually based on their length. A short text is one that has a maximum size of 255 characters. To support this, Microsoft Access provides a data type named Short Text. To specify that a column or field will use a short text:

• In the Datasheet View of a table
  • Click Click to Add and select Short Text
  • Click a cell in the design column, in the Add & Delete section, click the Short Text button
    
• In the Design View, after setting a field name, specify its Data Type as Short Text:
  

To assist you with common text fields, Microsoft Access provides many options in the Datasheet View. To use one of them, on the Ribbon, click Fields, in the Add & Delete section, click More Fields. In the Quick Start section, click the desired option.

If some columns exist already, to insert a text-based field between two columns, click the column header, or a cell under the column, that will precede it. On the Ribbon, click Fields. If a column has been created already and it is using a data type other than Short Text, to change it, click its column header or a cell under its column. On the Ribbon, click Fields. In the Formatting section, click the arrow of the Data Type combo box and select Short Text.

Practical Learning: Introducing Strings

1. Start Microsoft Access
2. In the list of files, click Altair Realtors1 from Lesson 17
3. In the Navigation Pane, double-click the Properties table to open it
4. On the table, click any cell under Property Type
5. On the Ribbon, click Fields
6. In the Add & Delete section of the Ribbon, click More Fields and click Address
7. On the table, right-click Country Region and click Delete Field

Text on a Form

As we have seen in previous lessons, after creating text-based field, you can add it to a form or report, in which case it would be used as a text box. A text-based column can also be used in a form or report as a combo box or any text-based control.

To access the string stored in a text box or any text-based control, use only the name of the control (don't add .Text)

Practical Learning: Using Text on Windows Controls

1. In the Navigation Pane, right-click the Properties form and click Design View
2. In the Property Sheet, click Data and change the Control Source of each control as follows:
    Control NameControl SourceAddressAddressCityCityStateStateZIPCodeZIPCode
3. Close the form
4. When asked whether you want to save, click Yes

Text as a Value

To indicate or specify the value of a string, put it between double-quotes.

Introduction to Strings and Expressions

As mentioned in the previous lesson, you can create an expression-based column on a table. The expression can include only constant values, only constant strings, only columns names, or a combination of those.

Practical Learning: Creating and Using Text-Based Expressions

1. On the Ribbon, click File and click Open
2. In the list of files, click FunDS1
3. In the Navigation Pane, double-click the Employees table to open it in the Datasheet View
4. On the form, click the empty cell under Last Name
5. On the Ribbon, click Fields
6. In the Add & Delete section, click More Fields, position the mouse on Calculated Field, and click Text
   

7. In the middle list of the Expression Builder dialog box, double-click FirstName
8. Type & " " &
9. In the middle list, double-click LastName
   

10. Click OK
11. Type EmployeeName in place of Field1
12. Click any cell under EmployeeName
13. In the Properties section of the Ribbon, click Name & Caption
14. Set the Caption as Employee Name and click OK
15. Close the table
16. In the Navigation Pane, right-click the Employees form and click Design View
17. In the Tools section of the Ribbon, click Add Existing Fields
18. From the Field List, drag EmployeeName and drop it on the form
19. Format its design to be like the other text boxes
20. Close the form
21. When asked whether you want to save, click Yes
22. In the Navigation Pane, right-click the ShoppingSessions form and click Design View
23. In the Controls section of the Ribbon, click the Combo Box control
    
    and click in the Detail section form
24. In the first page of the wizard, make sure the top radio button is selected and click Next
25. In the second page of the wizard, make sure Table: Employees is selected and click Next
26. In the Available Fields of the third page of the wizard, double-click EmployeeName and click Next
27. Don't change anything in the fourth page of the wizard and click Next
28. Don't change anything in the fifth page of the wizard and click Next
29. In the sixth page of the wizard, click the arrow of the combo box and select EmployeeNumber
30. Click Next
31. In the seventh page of the wizard, set the label as Processed By:
32. Click Finish
33. Close the form
34. When asked whether you want to save, click Yes
35. On the Ribbon, click File and click Open
36. In the list of files, click Ceil Inn1 from the previous lesson
37. In the Navigation Pane, double-click the Employees table to open it in the Datasheet View
38. On the form, click the empty cell under Last Name
39. On the Ribbon, click Fields
40. In the Add & Delete section, click More Fields, position the mouse on Calculated Field, and click Text
    

41. In the middle list of the Expression Builder dialog box, double-click FirstName
42. Type & " " &
43. In the middle list, double-click LastName
    

44. Click OK
45. Type EmployeeName in place of Field1
46. Close the table
47. In the Navigation Pane, right-click the Employees form and click Design View
48. In the Tools section of the Ribbon, click Add Existing Fields
49. From the Field List, drag EmployeeName and drop it on the form
50. Format its design to be like the other text boxes
51. Close the form
52. When asked whether you want to save, click Yes

Primary Charactistics of Strings

The Size of a String

The size of a string is the number of characters it contains or may hold. By default, when you have just created a Short Text-based field, whether in the Datasheet View or Design View, its default size is automatically set to 255 characters. In some circumstances, you may want the field to deal with a different size. You can therefore decrease or increase this size for any number between 1 and 255:

To specify the allowable number of characters of a Short Text-based field:

• In the Datasheet View, click a cell in the target column. On the Ribbon, click Fields. In the Properties section, click Field Size and change the value:

• In the Design View, in the top section, click the name, data type, or description of the column. In the lower section of the table, click Field Size and type the desired number.

As mentioned already, the default size of a Short Text-based field is set to 255. This value is set in the Access Options. To change it, open the dialog box (on the Ribbon, click File and click Options). In the Default Text Field Size spin button, enter the desired value:

After setting the Field Size property, the database would make sure that the user can only type so many characters.

Practical Learning: Setting Field Sizes

1. In the Navitation Pane, double-click the Properties table to open it in Datasheet View
2. On the table, click any cell under Address
3. On the Ribbon, click Fields
4. In the Properties section of the Ribbon, in the Field Size, click the value, type 100 and press Enter
5. Read the message box and click Yes
6. Right-click the tab of the table and click Design View
7. In the top section of the window, change StateProvince to State
8. In the top section of the window, change ZIPPostal to ZIPCode
9. Complete the fields as follows:
    Field NameField ZideCaptionPropertyNumberProperty #Address100City40StateZIPCodeZIP CodePropertyTypeConditionFinishedBasementPictures
10. Close the table
11. When asked whether you want to save, click Yes

The Length of a String

To get the size of a string, you can call a function named Len. Its syntax is:

Len(String) As Number

When calling this function, pass a string etween double-quotes or the name of a control. The function then produces the number of characters in it.

Practical Learning: Setting the Maximum Size of a Short Text Field

1. On the Ribbon, click File and click Open
2. In the list of files, click College Park Auto Repair1 from Lesson 16
3. In the Navigation Pane, right-click the JobsPerformed table and click Design View
4. In the top portion of the window, click JobName
5. In the bottom part, change the following characteristics:
   Field Size: 100
   Caption: Job Name
6. Save and close the table
7. In the Navigation Pane, right-click the PartsUsed table and click Design View
8. In the top portion of the window, click PartName
9. In the bottom part, change the following characteristics:
   ield Size: 80
   Caption: Part Name
10. Save and close the table
11. In the Navigation Pane, right-click the RepairOrders table and click Design View
12. Specify the fields sizes as follows:
     Field NameField SizeCustomerName50PhoneNumber20Address60City40State40ZIPCode20Make40Model40
13. Save and close the table

Input Masks

A mask is a technique of creating sections, also called placeholders, in a field. A section can be configured to accept only a letter, only a digit, a character or a digit, any symbol, nothing, or a particular character that the user cannot change. Microsoft Access supports two primary categories of masks: implicit and explicit. An implicit mask is one that is comtrolled by a data type. This means that, if you set a certain data type to a field, Microsoft Access can make sure that only valid values can be entered into the field.

An explicit mask is a field that is made to control what value is entered into it. When creating the mask, you will use some predefined characters and create a combination of your choice. A mask, or the type of mask, depends on the data type of the field.

You can create the mast for a field of a table or for a text box in a form. If you create the mask for a field in a table, when that field is added to a form, the mask will apply. If you change the mask of an existing field of a table after that field has been added to a form, the text box of that field will not be update on the form. You can either delete the text box on the form and re-add it to the form, or you can create the mask on the form.

To create a(n explicit) mask on a field:

• Display the table in Design View. In the top section of the window, click the field that needs the mask and specify its type. In the lower section of the window, click Input Mask
• Display the form in Design View. On the form, click the text box that needs the mask. In the Data tab of the Property Sheet, click Input Mask

In both cases, the field will be equipped with a browse button:.

Before creating a mask for a field, Microsoft Access comes with various pre-defind masks you can apply to a field. Masks are available for dates, times, US Social Security Number, currency values, etc. To help you apply one of these masks, Microsoft Access is equipped with the Input Mask Wizard. To open it, click the Browse button.

The first page of the wizard displays the most regularly used masks, including those for US/Canada telephone number, US Social Security #, US ZIP code, date, time, etc:

If you see a mask that suits you, you can check it by clicking the Try It text box. This would show the placeholders for the sections of characters that could be entered during data entry. If you see a mask you want to use, you can click it and click Next. If none of the masks suits your need, you can create a new one and add it to the list. To do this, click the Edit List button. This would open the Customize Input Mask Wizard dialog box:

By default, the Customize Input Mask Wizard offers four already created masks, including US/Canada phone number, US Social Security Number, US ZIP Code, and telephone extension number. You can check them by click the Next Record buttonin the bottom section of the dialog box. To create a new mask, click the New (Blank) Record button. After creating a new mask, you can click Close.

To complete the mask, continue with the wizard. If none of the masks provided by the wizard suits you, you can create your own. To do that, click the Input Mask property for the desired fields and uses the following characters to create the mask:

CharacterUsed to enter or accept0A single digit9A single digit or space#A digit, space, + or -LAn alphabetical character?A letterAA letter or a digitaA letter, a digit, or nothing&A character or spaceCA character, space, or nothing.A decimal place holder; for US English, this would be the period,Thousand separator; for US English, this would be a comma:;-/Date and time separator, as specified in the Regional Settings of Control Panel<A letter; the letter will be converted to lowercase>A letter; the letter will be converted to uppercase!Anything; the mask is filled from right to left for this position|Anything; the character that follows this one will be displayed itself. For example, if you type |L, the letter L would be displayed instead of being used a mask

If you want to include a word or sentence as part of the mask, type it in any section as desired.

Practical Learning: Using Input Masks

1. Re-open the Altair Realtors1 database
2. Display the Properties table in Design View
3. In the top section of the window, click State
4. In the lower section, click Input Mask, type >LL and press Enter
5. In the upper section of the window, click ZIPCode
6. In the lower section, click the Input Mask field and click its ellipsis button
7. When asked whether you want to save the table, click Yes
8. In the first page of the wizard, click ZIP Code
9. Click the Try It text box:
   

10. Click Next
    

11. Click Next
12. Click the first radio button
    

13. Click Next
14. Click Finish
15. Right-click the tab of the table and click Datasheet View
16. When asked whether you want to save the table, click Yes
17. Update the following records:
     Property #AddressCityStateZIP Code5248801640 Lombardo AveSilver Springmd20904-164968836410315 North Hacht RdAlexandriava222316114646366 Lolita DriveLaurelmd20707-1807749562495 Parker House TerraceGettysburgwv26901-110142711510232 Truesome DrvYorkpa174012004174140 Holisto CrtGermantownmd9274749522 Lockwood RdArlingtonva22203-82126826306114 Costinha AvenueMartinsburgwv25401-424028854010340 Helmes Street #408Silver Springmd20906-8006
18. Close the table

SQL and Strings

The SQL supports strings in two data types: CHAR, TEXT, and VARCHAR. They are the same as Microsoft Access's Short Text data type. Here are examples of using them:

CREATE TABLE Contractors
(
    FirstName CHAR,
    LastName TEXT,
    Gender CARCHAR
);

The char or the text data types are used for columns whose fields would receive (or present) text of 1 to 255 characters. If you want the field to hold a maximum of less than 255 characters, you must add parentheses to the data type and enter the maximum number in the parentheses. Here are examples:

What is a combo box Access?

What is the use of text box in the form?

What is a navigation form?

What is a label control in Access?