Identical Appearing Strings are <>

Question

I have a database with phone numbers. They are all Short Text, Field Size 15, Input Mask !\(999") "000\-0000;;_.

I can export these phone numbers to Excel. When I import them back into Access, they are no longer equal.

The actual field length of the original fields is 10 - presumably because there are no formatting characters included.

The actual field length of the imported fields is 14 - presumably because formatting characters have been added.

The receiving table for the import is a copy (structure only) of the originally exported table.

In a test query I join the tables on their primary keys and look at the phone number fields from both.

The LOOK exactly the same. However, the Expression [Orig].[Phone] = [Import].[Phone] comes up false.

This turn-around data has look like it hasn't changed - it hasn't.

What can I do to fix this? Any help would be appreciated.

---

Roger Reinsch

Answer 1

Hi Roger,

What is your definition of a "fix?"

If you merely want to make sure what "looks" the same passes a check for equality, then maybe you could try using Regular Expression when comparing the two values.

If you mean to make sure the imported data comes in as an exact copy of the exported data, then you'll have to find out what (invisible) characters are being added/removed and try to remove or put them back during the import process.

Just my 2 cents...

Answer 2

I suggest storing phone numbers as only digits, no formatting. Do the formatting when they are shown to the user. If you were storing international numbers then you might have the problem of the country code but that is not applicable here.

Trailing spaces is the most frequent cause of fields that your eyes tell you are equal yet the computer says they are not. Check for trailing spaces. The problem with fixed-length database fields is if the data does not fill the space then the extra space must be filled with spaces.

---

Sam Hobbs
SimpleSamples.Info

Answer 3

The culprit turned out to be Excel. It exports the field with formatting applied, whether you ask for formatting to be preserved or not. When it is imported, the formatting is not removed. So the fields don't match. The fix that causes the least ripple to the rest of the project is to build a query that strips out the formatting of 14 character phone numbers - [phone] - after the import to get back to just the 10 digits.

Another Excelism I recently encountered is that when I export a one column spreadsheet as a tab-separated text file, Excel insists on putting every row that has a comma in it inside double quotes. The "fix" for that was to replace all the commas with semicolons just before export. It's tough when your tool has a mind of its own...

---

Roger Reinsch

Answer 4

The culprit turned out to be Excel. It exports the field with formatting applied, whether you ask for formatting to be preserved or not.

It is actually Access that unconditionally applies the phone number format, not Excel. :/

---

Roger Reinsch

Answer 5

Hi Roger,

Glad to hear you got it sorted out. Good luck with your project.

Answer 6

It is actually Access that unconditionally applies the phone number format, not Excel. :/

Hi Roger,

Is it Access, or is it the input mask that you used?

In handling telephone numbers I never use input masks. After inputting a new number, I strip off all cosmetics, and analyse the the sequence of numbers whether it is a good number or not. This is dependant on several factors, including country number, area code, systematics from the telephone companies, etc. After checking and validation, the number is stored as string in the format the user likes. In this way the user can input telephone numbers in several ways, but always the preferred notation is returned.

As a detail, I can give a control on a form the property that it is a telephone field, and all the checks are done automatically, independant of the application.

Imb.

Answer 7

Let me address your comments in order.

I did not expect an input mask to effect exported data. The input mask seems to act as a presentation format for both input and output. I expected the export to serve up data in storage format rather than presentation format.

I have the same input mask applied to the table column and the data entry/update form. We only deal with USA phone numbers so a strict 10 digits format is OK. By using the mask on the form and the table definition, the data is actually stored as just the ten digits - no decoration is stored on disk. But it shows on forms (based on the text box control definition?) and the datasheet view (based on the table definition?) in the specified format. 

For my form, I use a text box with an event procedure for the before update event to record who made the change and when. Is the "telephone field" you mentioned a new object type? How do you implement it? Does it inherit from Text Box? I'm curious.

Roger

---

Roger Reinsch

Answer 8

Hi Roger Reinsch,

you had mentioned that imported data comes with the length of 14 digit.

i suggest you to change you datatype to number and try to convert text to number to store in database.

you said that Mask is just for presentation, so did you try to remove the mask and make a test?

if yes, does it make any difference? if you did not check it then you can try to test it.

you can also try to post your Excel file with sample data and sample Access Database with code.

we will try to make a test with it on our side and try to check the issue.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 9

Is the "telephone field" you mentioned a new object type? How do you implement it? Does it inherit from Text Box? I'm curious.

Hi Roger,

Apart from the formal declaration of a new object type and its methods and properties, I use little pieces of code that have the same effect.

In every application I use a metadata table that holds all the information about the controls, and how these controls should behave in different situations. The important events of the controls consult this table. When such a control has an indication of "Telephone field", then the "method" B_Telephone starts. This procedure gathers more information (if necessary e.g. Country) and does the checks. The same procedure B_Telephone is also triggered when code changes the control value, without user input.

As these methods/procedures are stored in a linked library database, they apply to all applications. Just by defining a specific control as "Telephone field" makes that it acts as such. Besides "Telephone field" you can define other checks, as "PostalCode", "BankAccount". free slots in any planning ...

The same systematic works also for AfterUpdate actions, and whatever you want.

Imb.

Answer 10

Hi Deepak,
I'm running up to a deadline (Saturday 8:00 AM) and can't do the experiments right now.
Next week I'll build a test table and form to nail down exactly which combinations of input mask produce what results.
I appreciate your offer to check your side.
Regards, Roger

---

Roger Reinsch

Answer 11

Your "method" appears to be an elaborate and elegant layer-on-top addition to the basic Access functionality. Thanks for explaining it to me and other forum watchers.
Roger

---

Roger Reinsch

Answer 12

Hi Roger Reinsch,

Is your issue resolved?

You did not done any follow up on this thread for a long time.

If your issue is solved then I suggest you to share your solution and mark it as an answer.

If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

If then after you have any further questions then you can let us know about it.

We will try to provide further suggestions to solve it.

I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

So take appropriate steps to close this thread , If your issue is resolved.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 13

When you export from Access be sure the "Export with Formatting and layout" is unchecked. But that will mean your Excel sheet will not have the formatting.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 14

I have created a small test database with one table with three columns/fields for phone numbers: number, text with input mask, and text with no input mask. My Saved Export requested "with format" which put the decorations on the phone numbers. When the Excel file is imported back into a copy of original table, the formatting is not removed. In datasheet view, the column with the input mask looks the same whether the underlying data is just 10 digits or whether it has the formatting characters included. When displayed, Access does format the column with the input mask for fields that are only 10 characters long butdoesn't apply the mask if the underlying data already has the format characters included. Datasheet view does not show exactly what is stored. Two different underlying data values, 1234567890 and [phone], both appear the same in datasheet view - but don't compare as equal.

The solution is to export without formatting so the imported data will be stored as 10 digits, not 14, and comparisons work.

---

Roger Reinsch