Word mailmerge problem w/ excel database 1

[joemajestee]

Hello,
I have a word 2003 doc that has a mergefield in it. As I step through the records, the numeric records display correctly but the alphanumeric records display only a 0 (zero).

The database is a spreadsheet in an excel workbook.

Data sample:
CatNum
1
2
3
12A
12MH
12G
13
14
...

Any thoughts?

 

[SkipVought]

hi,

NOT a good design to store NUMBERS and TEXT in the same column. You have experienced one of the results of this bad decision.

ALL your data in this column needs to be TEXT. You must CONVERT your numbers to TEXT.

Formatting the numbers as text will NOT convert the numbers to text.

Assuming that your column of data is not that long, the conversion can be accomplished by...
[tt]
1. FORMAT the data as TEXT
2. EDIT (F2 > ENTER) each cell
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi joemajestee,

The problem is due to the way Word determines the kind of data each field in the source contains. If it finds only numbers in the first 8 or so rows, the field is assumed to be numeric and nay non-numeric string evaluates to 0. One workaround is to ensure there's a representative text entry in the first record (eg by creating a dummy record) or, at least, in one of the first 8 records.

I don't agree with Skip's view that storing mixed numbers & text in the same column is necessarily a bad decision.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

I don't agree with Skip's view that storing mixed numbers & text in the same column is necessarily a bad decision.

Why would you mix NUMBER and TEXT and believe that that is not a bad decision?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi Skip,

It all depends on what the data represent. Valid 'mixed' data includes codes (eg part #s supplied by different manufacturers), Hex values (though not in this case - there's no 12MH or 12G), etc.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Valid 'mixed' data includes codes (eg part #s supplied by different manufacturers)

faq68-6659

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Another 'hint' that mixing numbers and text is a bad idea, is the difference between Excel & Access. In Access, you CANNOT enter numbers into a field defined as text, just as you cannot enter text into a field defined as numeric. Excel has many fewer rules for defining and entering data than does Access, and consequently, opens the door for many more mistakes.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

Thanks Skip.

Your solution did not work, unfortunately. I think I correctly implemented because I get the error "number stored as text"

 

[joemajestee]

Hi Paul,

I tried the dummy record and have experienced something odd. The merge field giving me the problem displays "11" which used to be in the first record. I have replaced it in the spreadsheet with 10G. The rest of the mergefields are correct. Stepping through the records, I get the same result, zeroes whenever alphanumeric is encountered.

Skip. I too, disagree. In the real world, you don't make the world fit your spreadsheet needs, you make the spreadsheet meet the world's needs. For the column in question, the leading numeric portion of the data is a straight-up category number. The A, MH and G further sub-categorize those entries and have particular meaning. Perhaps we could tell the world that it should refer to "Affordable" as 5 and "Garden" as 9 so as not to piss off Microsoft. Fortunately, Microsoft is not yet that powerful. In my case, this way of numbering has been around longer than Pentiums and is prescribed by a committee of non-computer types. Actually, they pay us computer types to figure it out. I try very hard not to tell the person paying me that it can't be done.

 

[SkipVought]

As a PROGRAMMER, you know that NUMBERS and DIGITS are two different animals. Therein is the issue!

Do you expect to do arithmetic on your 'numbers' in this column? I'd put my wager on NO. You have IDENTIFIERS in your column, not NUMBERS!

Convert your NUMBERS to TEXT (IDENTIFIERS) and all will be well.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

Skip, you are absolutely right in that the data should be treated as text. I cannot think of a situation where I would wish to perform arithmetic on the data. Still, the users use numbers and then sort by those numbers and we are stuck using their data.

Convert to text identifiers? Are you suggesting some other approach than your first post because that one didn't work.

Finally, FYI, when I signed up for this forum (which I love in no small part because of your contributions BTW) I had to categorize myself into a rigid choice of possible types of users. I know that I will mostly be asking questions about VBA so I put Programmer. However, I have no training, only experience. Go easy on me!

Thanks again.

 

[SkipVought]

Convert to text identifiers? Are you suggesting some other approach than your first post because that one didn't work.

[tt]
1. find a 'converted' entry that is ALL DIGITS.
2. in an empty cell, formatted GENERAL, enter those same digits manually, which will be a NUMBER
3. now in an empty cell, formatted GENERAL, enter a formula equating the cells in 1 & 2 above.
[/tt]
If the value in 1) is TEXT, the formula will return FALSE.

FYI, I have a BSEE, from the days when transistors were descrete components, and had never touched a computer for 2 1/2 decades. Tek-Tips has been a great learning forum.




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joemajestee]

OK, this is now solved. I used Paul's solution.

I shot myself in the foot by adding a second column to try to implement Skip's solution and named both columns the same, so my doc was grabbing the data from the inserted(test) column instead of the real column with the dummy record. I deleted that column and all is working.

Thanks, gentlemen!

 

[SkipVought]

Please state the exact solution you used.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi joemajestee,

After inserting the dummy row, Word might not play along until you disconnect the document from the data source, then reconnect. I suspect that, once Word has decided what kind a given field is, it might ignore any attempts to tell it otherwise. Hence the disconnect/connect remedy.

Cheers
Paul Edstein
[MS MVP - Word]