stevensc
asked on
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?
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
If Len(strZip) = 6 Then
strZip = Left$(strZip, 5)
End If
Jim
ASKER
I've got this under ControlSource of the last line of the mailing label =Trim([City] & ", " & [State] & " " & [Zip]). What do I do from there?
ASKER
I would like them to appear as Los Angeles, CA 90210-1010. Instead they are Los Angeles, CA 902101010.
ASKER
I may settle with cutting the zip code to 5 digits if that's the only option.
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
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
>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
Use ControlSource
=Trim([City] & ", " & [State] & " " & Format("00000-0000", [Zip]))
Cheers,
Dedushka
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim: I must disagree with you on this one. The 0 means false, which means it won't store the - character.
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
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
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.
ASKER
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)
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
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
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
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
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
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
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])
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],"@@@@@-@@@@")
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
I know! Let's play "I can write this funciton with the least amount of code"! :-))
We are of similar minds, my friend!
Jim
Thanks, Jim.
We have many others questions, so let's go :-)
Dedushka
We have many others questions, so let's go :-)
Dedushka
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