9 Digit Zip codes not printing with dash

[Bigdaddrock]

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:
ShipCityStateZi p: [Ship6] & ', ' & [Ship7] & ' ' & [Ship8]

I am baffled as to why this is happening.

Any suggestions?

Thanks for the anticipated assistance.

[mutanic]

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..

[Bigdaddrock]

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.

[MMcCarthy]

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.

[Bigdaddrock]

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.

[NeoPa]

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.

[Bigdaddrock]

9 Digit Zip code dropping the dash with sample file

Here is the sample file. Please UNHIDE the fields City, State, and Zip in the query which contains the questionable field ShipCityStateZi p.
Note that both the Zip and Ship8 fields contain the dash in the zip code, as well as the CityStateZip field. However, the ShipCityStateZi p continues to drop the dash.
Any solutions?

[NeoPa]

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.

[MMcCarthy]

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?

Mary

[NeoPa]

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.

[mshmyob]

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.

iif(Len(Ship8)-9,Left(Ship8,5) & "-" & Right(Ship8,4), Ship8)

Please note I have not tested the above code. Just thru it togther.

cheers,

[Bigdaddrock]

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:
ShipCityStateZi p: [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.
Thanks.

[OldBirdman]

I downloaded this program, and created the following query:

SELECT "~" & [ZIP] & "~" AS Z, "~" & [SHIP8] & "~" AS S
FROM [Test Table];

The results were:

      Z             S
~12205-3531~   ~122053531~
~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.

[Bigdaddrock]

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 ShipCityStateZi p 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 "#########",For mat([Ship8], "@@@@@-@@@@"), [Ship8]))

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

The result always produces the desired result.

Thanks to all who contributed suggestions as to how to cure my problem.

[OldBirdman]

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.

ShipCityStateZip: [Ship6] & ', ' & [Ship7] & ' ' & PostalCode(Ship8)