[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Problems with Input Mask Placeholder Character "-"

Posted on 1999-12-10
18
Medium Priority
?
895 Views
Last Modified: 2012-06-21
I am having troube with Input Mask Placeholder Character "-". My zip code is set to be input at the input mask of 00000\-9999;0;. However, when I try to print labels the report shows zip codes as, say, 902102100, that is, without that placeholder character "-". Zip code uses one field called Zip so I cannot really simpy change it (at least not that I know of). Can you help me solve this strange problem?
0
Comment
Question by:stevensc
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2273187
Stevensc:

If you use the input mask or format for a zip code, you are just looking at the field value with a mask laid over it.

This happens whether you use format or input mask.  Only the values required, the numbers are stored.  Therefore to get the values in your mailing label to appear correct, you will have to have the field output with a string function like this:

    strZIP = Left$(fldZIP, 5) & "-" & Mid$(fldZIP, 6)

That is about the only way that you can do this.  You could ask the user to provide the "-" or in the BeforeUpdate event, force the dashes with the same code.

Which place you do this depends on how you are creating your mailing labels.

Jim
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2273191
Whoops!  Of course you might want to test that Zip code to be sure that there is a full 9 digit code instead of five.  If the final length of the string is six characters, then use only the first five.

    If Len(strZip) = 6 Then
        strZip = Left$(strZip, 5)
    End If

Jim
0
 

Author Comment

by:stevensc
ID: 2273204
I've got this under ControlSource of the last line of the mailing label =Trim([City] & ", " & [State] & " " & [Zip]). What do I do from there?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:stevensc
ID: 2273207
I would like them to appear as Los Angeles, CA 90210-1010. Instead they are Los Angeles, CA 902101010.
0
 

Author Comment

by:stevensc
ID: 2273208
I may settle with cutting the zip code to 5 digits if that's the only option.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2273272
You can store the "-" in your input mask. You currently have:

00000\-9999;0;_

Change it to:

00000\-9999;1;_

(I can't remember for sure if that should be a 1 or a -1. Sorry.) The 0 between the two semicolons means that Access won't store the symbol used in the mask, i.e., the - here or in a SS#, the () in a phone number, etc. Try it with the 1/-1 between the semicolons and see if it works.

Of course, for all previously-entered values, the dash won't be put in; you'll have to do some kind of update query to get that, using the left$/right$ thing.

Hope this helps . . .

brewdog
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2273502
>I've got this under ControlSource of the last line of the mailing label =Trim([City] & ", " & [State] & " " & [Zip]). What do I do from there?

Use ControlSource
=Trim([City] & ", " & [State] & " " & Format("00000-0000", [Zip]))

Cheers,
Dedushka
0
 
LVL 7

Accepted Solution

by:
JimMorgan earned 800 total points
ID: 2273517
Steven:  By definition, your input mask should have stored the "-" as it is the "0" in the second part of the mask which determines is the literal character is stored as part of the value.  By chance, the Zip code is not a long number instead of a text data type?

The formula Dedushka gave you is backwards.  It would be Format([Zip],"00000-0000").  However, if the Zip code is only 5 characters long, "12345" would come out as "00001-2345" which is not what you want.

The only correct function would be

=Trim([City] & ", " & [State] & " " & Left$([Zip], 5) & left$("-", -1 *(Len([Zip])>5)) & mid$([Zip], 6))

This will handle both five and nine digit zip codes.  (We have written a lot of mailing programs.)

Jim
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2273964
Jim: I must disagree with you on this one. The 0 means false, which means it won't store the - character.
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2274282
brewdog:  Then the information within Access is wrong (which doesn't make me surprized).  This is from the help file:

The input mask definition can contain up to three sections separated by semicolons; for example, (999) 000-0000!;0;" ".

Section      Meaning

First      The input mask itself.

Second      Determines whether to store the literal display characters.

  0 = store literal characters with the value entered
  1 or leave blank = store only characters entered in blanks

Third      Character that is displayed for blanks in the input mask. You can use any character; type " " (double quotation marks, space, double quotation marks) to display a space. If you leave this section blank, the underscore ( _ ) is used.

Jim
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2274422
hmmm, maybe they changed it from Access 2, then. (I confess I haven't read the help file in 97 on this topic. I assumed it was the same way as in Access 2.) I stand corrected.
0
 

Author Comment

by:stevensc
ID: 2274431
Thanks JimMorgan, that did the trick. It is able to handle both 5 and 9 digits even if the user types less than 9 (say 8)
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2274467
Stevensc:

You're most welcome.  Glad I could be of help.

brewdog:  Never ASSUME anything.  I guess you know that that means.  Break it down   ASS U ME   :-)

Even old dogs can learn new tricks.

Jim

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2277519
Sorry, my friends, I don't agree.

1. If you want to handle both 9 and 5 digit ZIP codes you should use:
   - for long integer type =Format([ZipCode];"0-0000")
   - for text type field =Format([ZipCode];"&&&&@-@@@@")

2. If you can use only above mentioned Format function, you need not more complex formula with 4 text functions: Left$([Zip], 5) & left$("-", -1 *(Len([Zip])>5)) & mid$([Zip], 6))

Jim, what do you think about this?

Dedushka
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2277831
Dedushka:  Well, I do see that the expression and the format string are in the correct order.  :-)

I do agree that either one of these formats will work with 9 digit zip codes but with only a 5 digit zip code,
each will produce 1-2345 rather than 12345.

As far as item 2 is concerned, I don't see any difference in that than the one I provided.  Am I missing something?

If I wanted to make it absolutely foolproof I would have to add one other element, in case the "-" was already there.

 Left$([Zip], 5) & left$("-", -1 *(Len([Zip])>5) AND Mid$([Zip], 6, 1) <> "-") & mid$([Zip], 6)

Jim



0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2277948
JimMorgan:
Sorry, I understood that 9-digit code must be represent as "12345-6789" and 5 digit - as "1-2345".
BTW, our codes are 6 digit always, so we have no such problems.
What about
IIF(Len([ZIP])>5, Format([ZIP],"@@@@@-@@@@"), [ZIP])
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2278182
I sure that there are a variety of ways to do this.  This would be one provided that Zip does not have a '-' embedded.

I know!  Let's play "I can write this funciton with the least amount of code"!  :-))

We are of similar minds, my friend!

Jim
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2278277
Thanks, Jim.
We have many others questions, so let's go :-)

Dedushka
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question