Problem with numeric blank fields

[Iaacov Rosenberg]

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

 

[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]

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]

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]

Tanks a lot.
Iaacov

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