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]

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.