We help IT Professionals succeed at work.

Access forms not diplaying decimals after conversion to MDE

Dezzar82
Dezzar82 asked
on
Hi All,

I have a very strange issue that has only occurred recently with an application I developed - as in the problem was not present previously, and I cannot determine anything that has changed to make it occur now.

The problem is:
I have subforms whos control source is controlled dynamically by code.  User makes various selections which drive modules that forumlate the appropriate SQL to be used as the record source of the subform. All works well.

One of the fields on many of these subforms is a percentage.  Previously formatted as a percentage on the form itself.  The data is pulled directly from the table by the query without re-determining the format.  The table is formatted as double.

0.0255 would therefore display as 2.5500% on form.  And any calculated items in the query ie: (2.5/100) would display as 2.5000% (4 decimals)

All of a sudden, I have packaged up the latest version of the system, converted to MDE and deployed to users as their new front end.  Packaged up from exactly the same mdb as before.

Now, these very same percentage fields show simply as 2.0000  Decimal PLACES retained, but no information in them.  Its not rounding the numbers as 1.9888 displays as 1.0000

I have tried:
> Changing the query source to multiply the results or value by 100 and formatted the forms field as Fixed 4 instead of Percentage
> Started a new database and imported everything from the old database
>Ripping my hair out....

Still the same problem.  I notice that it only occurs once the system is converted to MDE.

Here are some screen shots:

The MDB version of the subform (Figure1) Figure 1
The exact same from the MDE version on the same computer (Figure2).... Figure 2
You might notice randomly that the third line from the bottom has decided to show the decimals in full, but is a completely different result to what it should be.  Apart from being an mdb or mde format database, there is not a single difference between the two...

For this particular form the field is a calculated field in a query ("([Margin$]/[Amount])*100 AS AvgMargin").  But the same occurs for non-calculated values as well.

This is quickly becoming one of the most frustrating issues I've ever had with access and I'm hoping someone out there has come accross this before, or knows what might be happening.
Comment
Watch Question

I am not sure what happend, and what changed, but you might create a back-up of the MDB and then try to Decompile it, and then Recompile it again, created the MDE again and see if that resolves your mystery problem.

See below on how to decompile:
http://www.granite.ab.ca/access/decompile.htm

Hope this helps,
Daniel
Bill RossProgrammer

Commented:
Hi,

Please post a sample of the db.  Perhaps it's something simple that has been there all along...

Bill
Commented:
All,

Thank you for your comments.  I've now found the cause of the problem...which....is a series of coincidences! Nothing to do with a suspect stange MDE conversion bug!

To explain in detail would take a while but basically, the import-export spec for a particular data set had been accidentally modified along the way which caused the data within the development version of the system to be fine, but the data coming into the user versions (mde's) to be incorrect, specifically around percentage values.

Have corrected the specification & now everything works as expected.

Thanks again!

Cheers
Dezzar!

Author

Commented:
Problem found.  Original assumptions leading to this post were incorrect.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.