This month, Ken takes on two questions: how to most efficiently store ZIP codes (and how to deal with printing or not printing the pesky hyphens), and how to set the starting page number for a report.
I’m having a problem storing postal codes. In some cases, I know the full nine-digit ZIP code, and in others, I only know the five-digit ZIP code. I understand that I can have the U.S. Postal Service provide me with the appropriate nine-digit ZIP codes for all my addresses, but I don’t want to go through that effort. The problem is that if I use an input mask that can handle nine-digit ZIP code correctly (with a hyphen after the fifth digit), it looks ugly when I print the data. All the five-digit ZIP codes end up with a hyphen following them. How would you solve this problem? Is there some other way to store the data that would make this work better?
Yes, this really is a problem. Using Access’ InputMask property seems like it ought to solve the problem, but it doesn’t. There’s no intrinsic logic to hide that trailing “-” for five-digit ZIP codes. In addition, using that input mask makes it very difficult to enter postal codes from more than one country in a single application. On the other hand, any answer I give here won’t be right for everyone, so I’ll just pick the solution I’ve used in a few client applications.
I think the simplest solution is to just give in and store the two chunks of the ZIP code in two separate fields. Because the only two uses you’ll normally make of the two portions of the ZIP code are data entry and reporting, it’s easy enough to modify both those uses to take advantage of the split field. The sample form, frmPostalCodes (see Figure 1) and the report, rptZipCodes, (see Figure 2) handle the split ZIP code in different ways. The form hides the text box that would allow you to enter the second portion of the ZIP code until you’ve entered something in the first portion (or move to a record that has its first portion filled in). Once there’s a value in the first portion of the ZIP code, the form displays a text box for entering the second portion. The sample report uses null propagation to display the hyphen only if it’s necessary.
How, then, do you use the split postal code? You’ll need to follow these steps (some of which might not apply to your particular application):
1. Modify the table design so it contains two fields for ZIP codes (I used PostalCode and PostalExt).
2. Fix up any existing data, splitting the postal codes into the two columns. If at all possible, modify the structure before you’ve entered data, because splitting up existing data requires, at best, an update query or two. If your data isn’t consistent, then you’ll also need to write an Access Basic/VBA function or two to help with the conversion.
3. Modify any queries that use the postal code fields.
4. Modify forms to allow for input of the new fields.
5. Modify reports that use the new fields.
6. Fix up any macros or modules that use the old field. If you don’t change the name of the base field (PostalCode, in my case), all your old code should at least function, if not completely correctly.
The following sections provide details on some of these steps.
Breaking up is hard to do
Taking an existing zipcode field and breaking it into two pieces is quite difficult, in general, but very easy, in specific cases. If you’ve stored a hyphen with your data, you can use an Update query, calling the InStr() function to search for the hyphen, and split on that location with the Left() and Right() functions. If you’ve stored only U.S. ZIP codes (with or without a hyphen), then you can simply take the left five characters for the first field, and the final four for the second.
If you’re storing U.S. and world-wide postal codes, and you’ve stored hyphens in with the text, you can look for the hyphen within the text, and split the fields based on that location. The InStr() function can locate one string within another. In this case, InStr() returns the numeric position of the hyphen within the old ZipCode field, or 0 if it can’t find a hyphen. The sample database in download file includes tblCustomerOld (borrowed, for the most part, from the Northwind sample database), which stores its postal codes in a single field, even though some of the codes are hyphenated. The sample database also includes a module, basPostalCodes, which includes two functions, GetPostalCode() and GetPostalExt(). You pass an existing postal code to each of these functions, and each returns one chunk of the split postal code, looking for a hyphen in order to split out the pieces. You can use those functions to take an existing column of postal codes and split them into the two pieces you need. Use these steps as an outline; you can try them out on the tblCustomerOld table in the sample database:
1. Make a backup copy of your table.
2. Modify the design of your table, and add PostalCode and PostalExt fields. (I’ve already done this for the sample table, tblCustomerOld).
3. Create an Update query (look for the query named qryFixPostalCode in the sample database in download file. Update the PostalCode field to be GetPostalCode([Postal Code]), and the PostalExt field to be GetPostalExt([Postal Code]), using your own field names, if they’re different. The sample query (qryFixPostalCode) does all this for you, for the sample data.
4. Run your query, updating all the rows.
The code in the GetPostalCode() and GetPostalExt() functions (shown here) is simple.In each case, the function uses the InStr() function to find the first occurrence of a hyphen, and returns the appropriate chunk of the string passed to it. (All code shown here is from the Access 2 version of the database, but the Access 95 version contains similar, though slightly modified, code.):
Const conDelimiter = "-" Function GetPostalCode (ByVal varValue As Variant) Dim intPos As Integer ' If the current value is Null, then ' just return Null. If IsNull(varValue) Then GetPostalCode = Null Else ' If the hyphen's there, return the left ' portion, otherwise return the full string. intPos = InStr(varValue, conDelimiter) If intPos > 0 Then GetPostalCode = Left(varValue, intPos - 1) Else GetPostalCode = varValue End If End If End Function Function GetPostalExt (ByVal varValue As Variant) Dim intPos As Integer ' If the current value is Null, then ' just return Null. If IsNull(varValue) Then GetPostalExt = Null Else ' If the hyphen's there, return the right ' portion, otherwise return nothing. intPos = InStr(varValue, conDelimiter) If intPos > 0 Then GetPostalExt = Mid(varValue, intPos + 1) Else GetPostalExt = Null End If End If End Function
If you’re storing just U.S. ZIP codes, and you’ve entered them with or without the hyphens, you can use the Left() and Right() functions to pull apart the old values. Use this to get the main portion:
Use this to get the extended portion:
Remember, this method works only for U.S. postal codes.
Once you’ve followed either of these methods, you should have two columns in your table, PostalCode and PostalExt, ready to use.
Entering postal code data
Once you’ve set up your table correctly, you can focus on getting new data into it. The sample form, frmPostalCode, shown in Figure 1, includes features that make data entry as simple as possible:
- As you move from row to row, hide or show the PostalExt text box, depending on whether there’s text in the PostalCode field. No point displaying the PostalExt data if there’s nothing in the PostalCode field!
- As you change the PostalCode field on the form, hide or show the PostalExt text box. If there’s nothing in the PostalCode field, hide the PostalExt field, of course.
- If you enter a hyphen into the PostalCode field, move the focus to the PostalExt field and throw away the hyphen keystroke.
To make these things happen, check out the code attached to frmPostalCodes. The first step, reacting to movement from row to row, takes place in the form’s Current event:
Private Sub Form_Current () ' If the current row has some text already, ' enable the text box. Dim fShow As Integer fShow = (Len(Me!txtPostalCode & "") > 0) Call ShowControls(fShow) End Sub Private Sub ShowControls (fShow As Integer) Me!txtPostalExt.Visible = fShow Me!lblHyphen.Visible = fShow End Sub
This code verifies that the length of the text in the txtPostalCode text box is greater than 0, and shows the hyphen and txtPostalExt text boxes accordingly.
To react to changes in the PostalCode text box, the form calls the following code from the Change event of that text box:
Private Sub txtPostalCode_Change () Dim fShow As Integer fShow = (Len(Me!txtPostalCode.Text & "") > 0) Call ShowControls(fShow) End Sub
Note the use of the Text property in the expression that checks the length. Because you’re caught in the act of editing the text, the default property (Value) of the text box hasn’t been set yet. You can retrieve only the Text property of a text box while it has the focus, and if you want to find out what’s in the text box while you’re in the midst of changing the value of the text box, you must use the Text property to retrieve the current value.
Finally, if you want to jump to the PostalExt text box when you press the separator key (by default, a hyphen) while in the PostalCode text box, react to the KeyPress event of the PostalCode text box, and set the focus to the PostalExt text box if the hyphen keystroke comes through:
Const conDivider = "-" Private Sub txtPostalCode_KeyPress _ (KeyAscii As Integer) ' If you press the divider key, then ' go to the PostalExt text box, ' and eat the keystroke. If KeyAscii = Asc(conDivider) Then Me!txtPostalExt.SetFocus KeyAscii = 0 End If End Sub
You must set the KeyAscii parameter to 0 on the way out of the routine, or a hyphen will show up in your text box. The whole point is that you use the hyphen to indicate that you’re ready to move to the PostalExt text box (and, of course, pressing Tab will give the same result), and by setting KeyAscii to 0 at the end of your code, you tell Access to “eat” the keystroke, and make it seem as if it had never happened.
Reporting postal code data
If you look carefully at the report shown in Figure 2, you’ll notice that there are no extraneous hyphens between the PostalCode and PostalExt fields. I covered the solution to a very similar problem in this column, in the October 1995 issue of Smart Access. The solution counts on Null propagation, a feature of Access that guarantees that if any term in an expression using “+” is Null, the entire expression will be Null. Normally, that’s not what you want when you’re working with strings, but in this case it is. If you want to avoid null propagation, use the “&” concatenation operator, which treats null values as empty strings, instead.
The address line of the labels shown in Figure 1 use the following expression as its ControlSource property:
=Trim([City] & (", " + [Region]) & " " & _ [PostalCode] & ("-" + [PostalExt]))
Because the final term of the expression uses a “+” sign between the “-” and the PostalExt field, if the field is Null, the output will be Null. By placing the final portion of the expression in parentheses, Access evaluates its value first, determines if it’s null, and then appends it to the rest of the expression. This way, if the PostalExt field is Null, you won’t see an extraneous hyphen.
I’m sure there are issues that I’ve neglected to dig into here, but by storing the postal codes in two fields, and following these suggestions for entering and displaying the two fields, you can handle most situations that come up.
I’m developing an application in which there are a wide variety of reports. At the end of a print run, I would like to make a management report with continuous page numbers, instead of each report beginning at page 1. I haven’t found a way to dynamically set page numbers in reports. Can you think of way to get there?
This problem comes up quite frequently. Because Access doesn’t supply any built-in way to set the starting page number for a report, you must find some way to do this manually. Let’s make sure readers are clear on what you’re requesting, however: the question is not how to start a report printing at a certain page (that is, for example, to start printing on the 15th page of a report) — that’s a different story altogether. The problem here is one of fooling Access into thinking the first printed page is actually page 15, for example, so that it prints page numbers starting with 15, then 16, and so forth. That way, when you place all your printed output together as a single report, the page numbers can be contiguous.
So what’s the trick? You can both set and retrieve a report’s Page property. You’ve probably used the Page property on a report to print the current page number in a text box. If you want to set the page number, though, you can do it from the Format event of the Report Header section. Then, when you ask Access to print the page number, it’ll use the new page number you’ve just set.
The only trick is to somehow request the starting page number when you open the report. That, too, is simple. I’ve written a simple function, SetStartPage (in basStartPage in the sample database in download file _GETZ34.EXE), that opens a small form (frmStartPage) in dialog mode, requesting a starting page number. (You could accomplish the same task with the InputBox function, of course, but you couldn’t get as fine control over the layout of the form.) Once you’ve pressed the OK button on the pop-up form, the code retrieves the value you’ve entered into the text box. Figure 3 shows the form in action.
The SetStartPage function is shown here:
Function SetStartPage () ' Open the form (conForm) and ' wait for the user to enter a starting ' page number. Return that number ' and close the form. Dim varValue As Variant DoCmd OpenForm conForm, , , , , A_DIALOG ' Wait here until the user's done... varValue = Forms(conForm)!txtStartPage If Not IsNumeric(varValue) Then varValue = 1 End If SetStartPage = varValue DoCmd Close A_FORM, conForm End Function
If the value that comes back in the form’s text box isn’t numeric, the code assumes you want to start printing with “1” as the page number.
Once the function is ready to go, calling it is simple. From the Format event procedure for the report, call the function like this:
Me.Page = SetStartPage()
This will call the function, retrieve the value for the first page number, and assign it to the Page property of the report.
To try this out, open rptPickPage from the sample database. As it opens, it’ll pop up the input form, wait for you to supply a value, and then it will preview the report for you with the page number you entered as the starting page number.