Let’s use the real-life example of Mike’s Music, which sells and rents new, used, and consigned instruments and equipment. The store also sells supplies, accessories, printed music, and recorded music; repairs instruments; and gives lessons.
When Mike’s Music opened, not much was known about what the store would stock, only that it would have guitars, strings, picks, supplies, and performance equipment. Mike is a singer-songwriter who had a basement with a home-recording studio and lots of guitars he’d collected over the years. Sometimes he loaned them out to others. The initial stock of guitars to sell and loan was small, but it wasn’t long before the inventory grew to include consigned instruments and equipment from others as well.
Mike found a warehouse that had been empty for a few years and approached the owner with a deal. In exchange for Mike making improvements to the space, he could use the space for no rent for six months, giving Mike a chance to build a successful business. The owner was happy at this prospect and agreed.
First a large room was built by the main entrance. In the front, guitars and accessories were hung on the walls and from the ceiling. Racks, shelves, tables—whatever Mike and his friends provided—were brought in to help display items. In the back, there was an area for lessons, which Mike gave after the store closed.
GL CODE INPUT MASK
Mike uses an Access database to help manage the business. (See bit.ly/3fX8Tqm for more examples of Mike Music’s Access use.) To track transactions for the general ledger, a pattern of xx-xxxxx-xxx-xx was used. Not counting the dashes, this is 12 characters. Figure 1 shows the meaning of each character by position.
Access includes an Input Mask property for fields in tables as well as controls on a form or report. An input mask consists of three sections that are separated with a semicolon. First is the mask pattern, composed of symbols that can be used for placeholders, separators, and actions, as shown in Figure 2.
The second section is either 0 or 1. If it’s set to 0, the literal characters will be stored with the data. If it’s set to 1 (or if nothing is specified), then the characters are only displayed, not stored. Whether or not you store them depends on whether or not you want them to appear when you copy or export data. It you want the value to be recognizable, then store the characters. If you might paste the data into another automated system, however, then you probably don’t want to store them. If the input mask changes and symbols aren’t stored, then all data, even that already created, will display according to the mask. If you do store the characters, the mask will only be applied as data is created or modified.
The third section of the input mask is the character that Access will display in each position where data needs to be entered. The default is an underscore (_).
DETERMINE SYMBOLS FOR A PATTERN
Using an input mask has worked out very well for the GL codes for the store transactions. As we go through each part of the GL code, we’ll look to see which input mask symbol works the best.
Position 1 is a digit for product condition, where 1=new, 2=used, and 3=consignment. In an input mask, 0 (zero) denotes a required digit, so that’s what we’ll use for this position.
Position 2 is a required letter indicating G=goods, S=services, or R=rental, so we’ll use A to represent a required letter. Then comes a literal dash.
Positions 3 and 4 describe the business section, which starts with a letter and then has another letter, digit, or space. Possibilities include S=string, W=wind, P=percussion, MB=music book, MS=music sheet, L=lesson, R=repair, P1=practice room 1, and so forth. These mask symbols will be L& to represent the required letter and then any character.
Positions 5, 6, and 7 are for product and style, such as G6A for a six-string acoustic guitar or Ami for a mini amp. Any characters can be entered—and all are required—so the mask symbols will be &&&. Then comes a literal dash.
Positions 8, 9, and 10 are for the manufacturer code, such as GIB=Gibson, FEN=Fender, and ELX=Elixir. This is three characters of any kind, so the mask symbols will be &&&. Then comes a literal dash.
Positions 11 and 12 are a two-digit code for purchase order or other plan. This is optional, so a pattern of 99 is used.
To specify the literal dash between groups, a pattern of - is used. The pattern starts with > to convert any letter to uppercase.
CREATE THE INPUT MASK
Now that we have a pattern, we have to decide what we want for the second and third sections. Mike chose to store the literal characters so the data had them when exported to Excel for analysis, so 0 was used to store symbols. He kept the default underscore for the display character.
Combining the parts of the pattern with the additional sections, the final input mask is >0A-L&&&&-&&&-99;0;"_". With this input mask, a value of 1gs g6agib25 becomes 1G-S G6A-GIB-25.
Download this month’s database: SF2006_TryInputMask.
SF SAYS
Use an input mask to validate the type of character in each position of a code and to display literal characters.
June 2020
