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