000000 display on upgrade to Windows XP

[David Greenwood]

I developed a database under SQL Server 2000, with Access 2000 on Windows
2000 as client. This had been running fine for several years. The client is
now upgrading to Windows XP, and has come across a display problem on the
reports. The figures are correct but are now displayed with lots of trailing
zeros e.g

365.000000

Sometimes there are 3 trailing zeros and sometimes six.
I have built a test system but cannot duplicate the problem :-( .

Has anyone seen this problem? I haven't found anything in the newsgroups or
any Knowlege Base article.

David

 

[Sylvain Lafontaine]

Take a look at it's Regional and Language Options in WinXP. Maybe the
number of decimal is greater than 2. You can also use a format string to
force the required number of decimal in your reports.

S. L.

 

[David Greenwood]

That was the first place we checked. It does not appear to be format
related. Infact, sometimes there are 3 zeros and sometimes 6 zeros! I shall
be at the clients office tomorrow to see if I can spot a pattern there.

This is a real nuissance. If we cannot find a workround then I shall have to
rewrite every form and report to wrap all fields in CINT(), CDBL() etc which
will be a real pita.

The strange thing is, I have not yet come across any reference to this type
of problem anywhere.

David

 

[Sylvain Lafontaine]

It's possible that this problem come from the fact that the VBA modules
inside the Access mdb (or ADP) file have not be compiled with the same
versions of VBScript and others DLL files.

Try the following: create a shortcut pointing to Access AND to the mdb file
and add the /decompile parameter at the end of it. Execute this shortcut at
least one time on the target machine (or on the developer's machine if they
are not Autoexec macro or an opening form with complex vbscript). This will
force Access to recompile all the modules using the libraries of the target
machine.

Simply doing a Compact/Repair is not sufficient, as opening Access with the
/decompile parameter but without any specified mdb file either.

This often work for me in resolving numerous problems that occurs when
deploying MDB and ADP files to different Windows versions.

S. L.

 

[David Greenwood]

Thanks for the suggestion. I have found the root cause of the problem. It
appears that Windows XP does not correctly interpret the data from SQL
Server "decimal" type fields. I also have a work aroung, which is to CAST
the data as INT or FLOAT on the server, as is appropriate. I'll try the
decompile. Otherwise its a case of modifying several dozen stored
procedures.

David

 

[David Greenwood]

Just one extra footnote: casting as decimal also fixes the problem, so the
complete statement of the problem/fix should be:

Windows XP misinterprets data from SQL Server when it is based on a
calculated value derived from a DECIMAL data type. Any explicit CAST on the
server cures this problem.

David