Problems with Input Mask Placeholder Character "-"

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?
stevenscAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimMorganCommented:
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
JimMorganCommented:
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
stevenscAuthor Commented:
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

stevenscAuthor Commented:
I would like them to appear as Los Angeles, CA 90210-1010. Instead they are Los Angeles, CA 902101010.
0
stevenscAuthor Commented:
I may settle with cutting the zip code to 5 digits if that's the only option.
0
brewdogCommented:
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
DedushkaCommented:
>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
JimMorganCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brewdogCommented:
Jim: I must disagree with you on this one. The 0 means false, which means it won't store the - character.
0
JimMorganCommented:
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
brewdogCommented:
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
stevenscAuthor Commented:
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
JimMorganCommented:
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
DedushkaCommented:
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
JimMorganCommented:
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
DedushkaCommented:
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
JimMorganCommented:
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
DedushkaCommented:
Thanks, Jim.
We have many others questions, so let's go :-)

Dedushka
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.