Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Iaacov Rosenberg
 
Posts: n/a
Default Problem with numeric blank fields

Hi,

I noticed a flaw in Word Mail Merge using MSQuery.

If a numeric field is blank (i.e. null value in DB), Word duplicates the
previous record value for that field. This doesn't happen with strings or
dates.

Conditional formatting is not an option because the field already has the
wrong (=previous) value.

I could go back to the DB and generate a view which places special values
instead of nulls so I could have tested for it in Word. This can't be
implemented as a generic solution (each field might require a different
special value) and is highly undesirable.

Does anybody know about a better workaround?

Thanks,
Iaacov


  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default Problem with numeric blank fields

I have come across this, or something similar, before (in fact I think there
is a Knowledgebase article about it, but I just looked and could not find
it). I thought it had been fixed, so...

The very simple test I just set up did not reveal the problem, so it would
help if you could be more specific, i.e.
- which version of Word/office?
- which type of database? (and are you seeing the problem with several
types of database)
- is this with a particular type of numeric data (e.g. a "currency" field)
or all types?

In this case, until I can replicate the problem I can't test possible
solutions. Unless there is an update that fixes the problem, the only thing
I can think off is to modify the SQL directly in MS Query (i.e. rather than
create a View in the database), or in a VBA OpenDataSource call, to do
something along the lines of:

SELECT ... iif(isnull(myfield),0.0,myfield) AS `myfieldx`,...

However, problems with that approach include:
a. the code may need to be more complex to cope with all possible types of
empty field
b. it may not work if if myfield evaluates to the value in the previous
record before the isnull function is evaluated.
c. you may not want a "0.0" result in that case. but you could at least
create a new field saying whether the original field was null or not
d. the precise code depends on the dialect of SQL (i.e. is not generic -
unless you can use ODBC escapes in this case - Id have to check)
e. the code would have to be manually created for each data source.
f. The code for each number is fairly long, and if the data source has a
lot of columns the SQL query text may easily break Word's length limit
(either 255 or 511 characters)

Best I can do for now.

Peter jamieson
Also, you may not
Can you be more specific:
version of Word/office?



"Iaacov Rosenberg" wrote in message
...
Hi,

I noticed a flaw in Word Mail Merge using MSQuery.

If a numeric field is blank (i.e. null value in DB), Word duplicates the
previous record value for that field. This doesn't happen with strings or
dates.

Conditional formatting is not an option because the field already has the
wrong (=previous) value.

I could go back to the DB and generate a view which places special values
instead of nulls so I could have tested for it in Word. This can't be
implemented as a generic solution (each field might require a different
special value) and is highly undesirable.

Does anybody know about a better workaround?

Thanks,
Iaacov



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Iaacov Rosenberg
 
Posts: n/a
Default Problem with numeric blank fields

I found the problem in Word XP, either the original version or SP3. Didn't
check other versions.

Thanks to your questions I went for a little research...
Here are my findings:

I tested with 2 databases: Oracle 8i and ASA 8.03.

Oracle has 2 numeric types: NUMBER and FLOAT.
The Oracle test worked fine. I tested with NUMBER(3), NUMBER(5,2) and
FLOAT(5).

ASA has several integer types, 3 floating point types (FLOAT, DOUBLE, REAL)
and 2 exact decimal types (DECIMAL and NUMERIC).
In the ASA test, only NUMERIC failed.

I checked the ODBC log to find out what ODBC types they were bound to.
DECIMAL was bound to the SQL_DECIMAL ODBC datatype and NUMERIC to
SQL_NUMERIC. According to ODBC reference both types differ only in their
precision handling.
Not surprisingly, the Oracle ODBC driver converts NUMBER to SQL_DECIMAL.

I also verified that inside MSQuery, all types behave correctly.

The solution I can see is to cast all NUMERIC to DECIMAL in MSQuery, but I'm
still left with some of the problems you mentioned (d, e and f).

Any better idea?

Thanks,
Iaacov

"Peter Jamieson" лъб
...
I have come across this, or something similar, before (in fact I think
there is a Knowledgebase article about it, but I just looked and could not
find it). I thought it had been fixed, so...

The very simple test I just set up did not reveal the problem, so it would
help if you could be more specific, i.e.
- which version of Word/office?
- which type of database? (and are you seeing the problem with several
types of database)
- is this with a particular type of numeric data (e.g. a "currency" field)
or all types?

In this case, until I can replicate the problem I can't test possible
solutions. Unless there is an update that fixes the problem, the only
thing I can think off is to modify the SQL directly in MS Query (i.e.
rather than create a View in the database), or in a VBA OpenDataSource
call, to do something along the lines of:

SELECT ... iif(isnull(myfield),0.0,myfield) AS `myfieldx`,...

However, problems with that approach include:
a. the code may need to be more complex to cope with all possible types of
empty field
b. it may not work if if myfield evaluates to the value in the previous
record before the isnull function is evaluated.
c. you may not want a "0.0" result in that case. but you could at least
create a new field saying whether the original field was null or not
d. the precise code depends on the dialect of SQL (i.e. is not generic -
unless you can use ODBC escapes in this case - Id have to check)
e. the code would have to be manually created for each data source.
f. The code for each number is fairly long, and if the data source has a
lot of columns the SQL query text may easily break Word's length limit
(either 255 or 511 characters)

Best I can do for now.

Peter jamieson
Also, you may not
Can you be more specific:
version of Word/office?



"Iaacov Rosenberg" wrote in message
...
Hi,

I noticed a flaw in Word Mail Merge using MSQuery.

If a numeric field is blank (i.e. null value in DB), Word duplicates the
previous record value for that field. This doesn't happen with strings or
dates.

Conditional formatting is not an option because the field already has the
wrong (=previous) value.

I could go back to the DB and generate a view which places special values
instead of nulls so I could have tested for it in Word. This can't be
implemented as a generic solution (each field might require a different
special value) and is highly undesirable.

Does anybody know about a better workaround?

Thanks,
Iaacov






  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default Problem with numeric blank fields

Any better idea?

No, sorry.

Assuming there are two possible problems:
a. the ASA ODBC driver is not returning the data correctly
b. MS Query/Word are not dealing with SQL_NUMERIC data correctly

I suppose I would have to try to seek a solution from ASA for (a) and from
Microsoft directly for (b) (I don't work for them - you would need to
contact their support function or Customer services directly).

What I do know is that there are, or have been, problems with Currency data
in Access and Excel in the past. Those problems could well be related to the
one you are seeing. When I looked at the problem several years ago, my best
guess was that there might be an an error somewhere in a numeric routine
used in more than one place in MS software. It really was nothing more than
a guess, but perhaps someone in Microsoft knows more about it.

Peter Jamieson



"Iaacov Rosenberg" wrote in message
...
I found the problem in Word XP, either the original version or SP3. Didn't
check other versions.

Thanks to your questions I went for a little research...
Here are my findings:

I tested with 2 databases: Oracle 8i and ASA 8.03.

Oracle has 2 numeric types: NUMBER and FLOAT.
The Oracle test worked fine. I tested with NUMBER(3), NUMBER(5,2) and
FLOAT(5).

ASA has several integer types, 3 floating point types (FLOAT, DOUBLE,
REAL) and 2 exact decimal types (DECIMAL and NUMERIC).
In the ASA test, only NUMERIC failed.

I checked the ODBC log to find out what ODBC types they were bound to.
DECIMAL was bound to the SQL_DECIMAL ODBC datatype and NUMERIC to
SQL_NUMERIC. According to ODBC reference both types differ only in their
precision handling.
Not surprisingly, the Oracle ODBC driver converts NUMBER to SQL_DECIMAL.

I also verified that inside MSQuery, all types behave correctly.

The solution I can see is to cast all NUMERIC to DECIMAL in MSQuery, but
I'm still left with some of the problems you mentioned (d, e and f).

Any better idea?

Thanks,
Iaacov

"Peter Jamieson" лъб
...
I have come across this, or something similar, before (in fact I think
there is a Knowledgebase article about it, but I just looked and could not
find it). I thought it had been fixed, so...

The very simple test I just set up did not reveal the problem, so it
would help if you could be more specific, i.e.
- which version of Word/office?
- which type of database? (and are you seeing the problem with several
types of database)
- is this with a particular type of numeric data (e.g. a "currency"
field) or all types?

In this case, until I can replicate the problem I can't test possible
solutions. Unless there is an update that fixes the problem, the only
thing I can think off is to modify the SQL directly in MS Query (i.e.
rather than create a View in the database), or in a VBA OpenDataSource
call, to do something along the lines of:

SELECT ... iif(isnull(myfield),0.0,myfield) AS `myfieldx`,...

However, problems with that approach include:
a. the code may need to be more complex to cope with all possible types
of empty field
b. it may not work if if myfield evaluates to the value in the previous
record before the isnull function is evaluated.
c. you may not want a "0.0" result in that case. but you could at least
create a new field saying whether the original field was null or not
d. the precise code depends on the dialect of SQL (i.e. is not generic -
unless you can use ODBC escapes in this case - Id have to check)
e. the code would have to be manually created for each data source.
f. The code for each number is fairly long, and if the data source has a
lot of columns the SQL query text may easily break Word's length limit
(either 255 or 511 characters)

Best I can do for now.

Peter jamieson
Also, you may not
Can you be more specific:
version of Word/office?



"Iaacov Rosenberg" wrote in message
...
Hi,

I noticed a flaw in Word Mail Merge using MSQuery.

If a numeric field is blank (i.e. null value in DB), Word duplicates the
previous record value for that field. This doesn't happen with strings
or dates.

Conditional formatting is not an option because the field already has
the wrong (=previous) value.

I could go back to the DB and generate a view which places special
values instead of nulls so I could have tested for it in Word. This
can't be implemented as a generic solution (each field might require a
different special value) and is highly undesirable.

Does anybody know about a better workaround?

Thanks,
Iaacov








  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Iaacov Rosenberg
 
Posts: n/a
Default Problem with numeric blank fields

Tanks a lot.
Iaacov

"Peter Jamieson" лъб
...
Any better idea?


No, sorry.

Assuming there are two possible problems:
a. the ASA ODBC driver is not returning the data correctly
b. MS Query/Word are not dealing with SQL_NUMERIC data correctly

I suppose I would have to try to seek a solution from ASA for (a) and from
Microsoft directly for (b) (I don't work for them - you would need to
contact their support function or Customer services directly).

What I do know is that there are, or have been, problems with Currency
data in Access and Excel in the past. Those problems could well be related
to the one you are seeing. When I looked at the problem several years ago,
my best guess was that there might be an an error somewhere in a numeric
routine used in more than one place in MS software. It really was nothing
more than a guess, but perhaps someone in Microsoft knows more about it.

Peter Jamieson



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I insert a placeholder for blank Word merge fields? DJ Mailmerge 1 March 15th 06 06:48 PM
Correct spacing for blank fields in name mailmerge Harry Weeds Mailmerge 3 March 5th 06 08:58 AM
blank merge fields Scott Mailmerge 1 February 24th 06 06:01 AM
omit blank mail merge fields gapkid Mailmerge 2 October 20th 05 07:19 AM
Blank fields when mail merging Romina Mailmerge 1 December 13th 04 09:28 AM


All times are GMT +1. The time now is 04:51 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"