473,320 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 7383
mutanic
19
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
MMcCarthy
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
NeoPa
32,556 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, 142 views)
Oct 11 '10 #7
NeoPa
32,556 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
MMcCarthy
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?

Mary
Oct 13 '10 #9
NeoPa
32,556 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
mshmyob
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.

cheers,
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.
Thanks.
Oct 18 '10 #12
OldBirdman
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
OldBirdman
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
Oralloy
985 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)
  2.  
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.

Luck!
Oct 18 '10 #16
NeoPa
32,556 Expert Mod 16PB
BigDadDrock:
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.
Hmm.....
Oct 19 '10 #18
NeoPa
32,556 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

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

Similar topics

7
by: csumner | last post by:
I am trying to use the haversine function to find the distance between two points on a sphere, specifically two zip codes in my database. I'm neither horribly familiar with SQL syntax nor math...
6
by: Peter Foti | last post by:
I have seen lots of examples where some HTML text is replaced with a background image using CSS. For example, replacing the text of an <h1> with a graphical logo, like so: CSS: h1 span {...
2
by: Bobbie | last post by:
I am trying to make mailing labels. The zip/postal codes are 9 digits (ex: 40601-0223) I ran a query and put in the input mask 00000/-9999;;_ and it works but when I make the mailing labels and...
1
by: CM | last post by:
Hi, I'm thinking about writing .net codes (asp.net, vb.net) for printing management, such as keep tracking of who prints what on when...etc. The app will need to find all available network...
17
by: Kermit Piper | last post by:
Hello, I have been searching, Googling, searching. Cannot find a javascript to calc 10, 11 or 12 digit UPC codes. I just need an algorithm that calcs to verify the correct check digit. I have...
4
by: sukhsinghin | last post by:
Hi, I am trying to print a invoice form with data from dataset using vb.net and printer is dot matrix. Invoice is a continues paper with pre-printed headers like First Name, Last Name, SSN# etc....
0
by: prabhuvonnet | last post by:
Hello all, I need to convert this following vb.net codes to c# codes within a day or two. So i need this help urgently. Please look at the following code and help me out to finish this barcode...
10
by: D | last post by:
Hello everyone - I'm trying to compile an application to generate all possible 3 digit combinations using 0-9 and a-z, I've looked everywhere for a solution and I found Combinations! v 2.0 for...
8
by: Marc | last post by:
Hi all, I have to generate and send to a printer many 6 digit alphanumeric strings. they have to be unique but I cannot check in a database or something like that if it have already been printed....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.