469,356 Members | 1,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

9 Digit Zip codes not printing with dash

I have a table that has two fields that are identically formatted TEXT boxes with field size of 12 (more than necessary, but memory is not an issue). On both I have the Input Mask set for Zip Codes, formatted as "00000\-9999;0;_". Heck, I even selected to save the Zip Codes with the dash.

The two identical fields are ZIP and SHIP8. The first is used for billing address, the latter is for shipping.

My problem arises in a query. I have a field for each of ZIP and SHIP8. And both show the nine digit zip codes with a dash. However, when I use them in n expression, only ZIP appears with the dash, the SHIP8 comes out as a nine digit number.

Here are the two expressions.
The successful one is:
CityStateZip: [City] & ', ' & [State] & ' ' & [Zip]

The failing one is:
ShipCityStateZip: [Ship6] & ', ' & [Ship7] & ' ' & [Ship8]

I am baffled as to why this is happening.

Any suggestions?

Thanks for the anticipated assistance.
Oct 8 '10 #1
18 6812
i'm not a pro, maybe you can set field Ship8 format same as field ZIP...

my friend also said it could be because you use input mask instead just number..
Oct 8 '10 #2
Both fields do have the exact same format.

I must use an input mask instead of number, since the nine digit code has a dash - a number field would not recognize it as such.
Oct 8 '10 #3
14,534 Expert Mod 8TB
You don't say if these text box controls are bound to particular fields but since you are querying them I assume they are.

You have said that the formatting of the text boxes are the same but you need to be more concerned about the formatting of the fields. It definitely sounds like the fields have different data types.
Oct 8 '10 #4
The text boxes ZIP and SHIP8 are both bound to the Table of Addresses. They have the exact same format in the table, and I have done nothing to amend how they each appear in the Query. As such, I am befuddled as to why the Zip continues to carry the dash, but the Ship8 drops it when each is incorporated into a Built expression.
Oct 9 '10 #5
32,185 Expert Mod 16PB
My reading is also that there is something, somewhere, which is different between the two. I don't mean to imply you're lying. Simply that there are various elements that people don't often see, which may be where the difference lies.

If you would like to attach a sanitised copy (General instructions below) I could have a look at it for you. I don't have anything beyond 2003 installed at this time though in case it's a 2007 or 2010 database.

When attaching your work please follow the following steps first :
  1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
  2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
  3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
  4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
  5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
  6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
  7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
  8. Compress the database into a ZIP file.
  9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.
It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.
Oct 9 '10 #6
Here is the sample file. Please UNHIDE the fields City, State, and Zip in the query which contains the questionable field ShipCityStateZip.
Note that both the Zip and Ship8 fields contain the dash in the zip code, as well as the CityStateZip field. However, the ShipCityStateZip continues to drop the dash.
Any solutions?
Attached Files
File Type: zip Zipcode Test Database.zip (9.3 KB, 123 views)
Oct 11 '10 #7
32,185 Expert Mod 16PB
I've looked at your database (I managed to unhide the fields after digging around the interface for a while, but I'm not clear why you sent them hidden). The fields are indeed identical in all manners that I could see (and I checked everything I could conceive of).

The only difference I could find was in the data. I assume you added the existing [SHIP8] data by some form of copying from [ZIP], or even possibly added the data in before making the change to the design to incorporate the mask. I don't really know how you loaded it differently, but somehow you did. When I entered new data into [SHIP8] using the mask configured, it behaved correctly in the query - just like the [ZIP] data did.
Oct 11 '10 #8
14,534 Expert Mod 8TB
As NeoPa says it's got something to do with how you copied the data into the ship8 field.

In the zip field the data is being stored with the hyphen character. In the ship8 field it is not being stored with the character.

My advice is to recreate the formats or set up a saved template for them. There is an option at the end of the creation process which asks whether you want to store the number with the hyphen or without. You may have missed that option on the ship8 format.

Only other explanation is as NeoPa said the problem is arising when you import the data to ship8. It's imposing something. Can I ask if the data you are importing actually has a hyphen in it?

Oct 13 '10 #9
32,185 Expert Mod 16PB
Just to be clear, the field definitions are not the issue. It is certainly the data loaded in that was a problem. New data, entered in via the form, works perfectly as expected for both fields. It is only the historically entered data that shows this problem at all. You can go forward confidently with your current design in the full knowledge that this problem won't recur as long as you are loading the data in normally using the form.
Oct 14 '10 #10
904 Expert 512MB
I believe what you did was:

1. You orginally had the Ship8 field as text but no format string
2. You entered all you Shipping ZIP codes without the dashes.
3. After entering your shipping ZIP codes you then changed the mask on that field to the ZIP mask.

This replicates the problem you are currently having.

Is this what you did?

If you did then you have to remember that the mask only is used for DISPLAY purposes and has not actually changed your data - in other words it is still stored in the table without the hyphens.

You will need to do an update query and add a hyphen between the 5th and 6th character.

Expand|Select|Wrap|Line Numbers
  1. iif(Len(Ship8)-9,Left(Ship8,5) & "-" & Right(Ship8,4), Ship8)
Please note I have not tested the above code. Just thru it togther.

Oct 14 '10 #11
Thank you for all of your suggestions. And thanks for clarifying that the Input Mask is indeed just for display purposes.
However, what continues to concern me is the following:
If Ship8 shows the item with a dash, then I must assume it is being kept with the dash. Why would the dash be dropped when used in the expression:
ShipCityStateZip: [Ship6] & ', ' & [Ship7] & ' ' & [Ship8]
As for how it is being "imported", I have tried editing the Ship8 field with a totally new zip code (containing the dash) and yet still have the same problem with the dash being dropped in the expression.
Please do not give up on me, I need to address this in a client's database.
Oct 18 '10 #12
675 512MB
I downloaded this program, and created the following query:
Expand|Select|Wrap|Line Numbers
  1. SELECT "~" & [ZIP] & "~" AS Z, "~" & [SHIP8] & "~" AS S
  2. FROM [Test Table];
The results were:
Expand|Select|Wrap|Line Numbers
  1.       Z             S
  2. ~12205-3531~   ~122053531~
  3. ~44004-3756~   ~440043756~
I would conclude that the data is in the table differently, not that a dash(-) is being dropped during an expression. As the properties for these two fields (ZIP & SHIP8) appear to be the same now, I would conclude that some property was set in the past, now fixed, but the effects linger. I could not find it.
I am posting this to focus attention on the table, not on the code.
Oct 18 '10 #13
I have found a solution to my issue. It is based on an MS article found at http://support.microsoft.com/kb/207829

I adapted it to my database and found that if I did the following, I ensured that each time the ShipCityStateZip came up it included the dash, if it had a nine digit zip code.

The solution was to create a new field in the query called Postal Code:

Postal Code: IIf([Ship8] Like "#####-", Left([Ship8], Len([Ship8])-1), IIf([Ship8] Like "#########",Format([Ship8], "@@@@@-@@@@"), [Ship8]))

I then revised my ShipCityStateZip field to be as follows:
ShipCityStateZip: [Ship6] & ', ' & [Ship7] & ' ' & [Postal Code]

The result always produces the desired result.

Thanks to all who contributed suggestions as to how to cure my problem.
Oct 18 '10 #14
675 512MB
You have found a way to hide your problem, not a solution.
That is OK, for now. In the future, if you try to compare ZIP with SHIP8, or do a JOIN in a query, or some other common operation, your problem will resurface.
I would suggest that you write your 'Work-around' as a Public Function (maybe named PostalCode) so you can use it in a variety of places in the future.
Expand|Select|Wrap|Line Numbers
  1. ShipCityStateZip: [Ship6] & ', ' & [Ship7] & ' ' & PostalCode(Ship8)
Oct 18 '10 #15
983 Expert 512MB
Is there any chance you can update the table, so that the field contains the correct representation in all cases? Something like:

Expand|Select|Wrap|Line Numbers
  1.   UPDATE mnyTable SET Ship8=PostalCode(Ship8)
Also, as an aside, you might want to build a data conditioning/recovery stored procedure, so you can "fix" the database, next time someone decides to enter inconsistent data.

Oct 18 '10 #16
32,185 Expert Mod 16PB
Please do not give up on me, I need to address this in a client's database.
Did you read my earlier posts (#8 & #10)? They were formed after a great deal of checking into your database.

I thought the issue was completely resolved at that point. What do you still see as a problem from that position, if anything?
Oct 19 '10 #17
You may be onto something there. I too found that if I added the nine digit zip code directly into the table, it carried the dash through to the Query.
What I found is that I am populating new records in a different table based on an existing record in a separate table using the SetValue macro in a form.
More specifically, I am using the Zip Code in a Customer Table to populate the Zip Code and SHIP8 columns in an Invoice Table.
Is it possible that the SETVALUE command is impacting the structure of the Zip Code and SHIP8 values in the INVOICE TABLE?
And yet, as I indicated in my last reply to this chain, I cannot understand why the Zip Code and SHIP8 values appear with the dash, yet only drop the dash when used in the Expression "CityStateZip".
This leads me to think that the issue I am trying to resolve must reside in the construct or restrictions of an expression using a nine-digit formatted zip code. Somehow it is not "seeing" the dash when it merges the Zip with the City and State.
Oct 19 '10 #18
32,185 Expert Mod 16PB
From that I would guess that how you're populating the data is a problem. Your description of that is not much to go on though. Post the code you use if you'd like me to check over it for you. VBA is always better, but if you're using a macro then post what you have anyway.

As for the data showing up one way in one situation and another in the other, I would say that as this only happens when you populate the data the way you do, don't worry about that as such. get to where the data is properly populated then it works in all circumstances. Nothing left to worry about.
Oct 20 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by Kermit Piper | last post: by
8 posts views Thread by Marc | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.