Printing an Access report and decimal place weirdness

views:

131

answers:

Printing an Access report and decimal place weirdness

My client has an Access (2000) application that we didn't write and we don't have access to the MDB (it's an MDE). One of the functions is to create a packing slip report. There's no option to preview, only print or save to file.

There's a field that represents a weight; it's a Double field. On a standalone machine, it prints correctly, but when printing through Terminal Services it displays all zeros. Printing to XPS format, however, allowed us to see that it was formatting the number to about twenty decimal places, which suggests to me on the standalone machine it may be doing the same thing but left-aligning the field, but right-aligning (and thus displaying only the zeros) through Terminal Services.

For what it's worth, I had nothing to do with this, but our network guy brought it to me. I can get more info if needed. Any ideas what may cause this to happen and how to fix it?

It's possible that the default printer on the server is formatting the report differently. A similar thing happens with Crystal .NET for people in our shop who have different default printers - sometimes elements close to the margin don't show, sometimes they clip, sometimes they are fine. If possible, change the default printer on the Terminal Server to the same printer as on the "standalone machine", as a test.

HardCode 2009-12-17 22:23:08

+1 A:

The only possible fix is properly formatting the field and that requires the MDB source code. Sorry, but that's the only real solution.

David-W-Fenton 2009-12-18 01:47:41

...And while they are in there they should change the data type to `DECIMAL` with appropriate values for scale and precision.

onedaywhen 2009-12-18 10:43:16

You are aware of the bugs with the Jet decimal type when used in Access, right? Incorrect sorting with Decimal fields: http://allenbrowne.com/bug-08.html . I seem to recall that this has been patched in A2007, but I can't find any evidence of that trolling the Knowledge Base, and Allen Browne keeps on top of these things, so if he doesn't mention it, it must mean I'm misremembering.

David-W-Fenton 2009-12-19 04:28:27

"You are aware of the bugs with the Jet decimal type when used in Access, right?" -- I am aware of one bug, being the negative sort order bug. This was indeed fixed in ACE 2007. I believe Allen Browne acknowledges the bug fix here: http://allenbrowne.com/tips.html : "Incorrect Sorting (Decimal fields) Access 2000 on [sic] (partially fixed in 2007)".

onedaywhen 2009-12-21 10:18:29

"so if he doesn't mention it, it must mean I'm misremembering" -- methinks you hold the man it too high regard. Did you test his assertions e.g. "Nulls and zeros sort unpredictably - at the beginning, middle or end, depending on the data" -- this doesn't stand up to scrutiny. Test it yourself and you will find that all zeros are always sorted together with the positive values...

onedaywhen 2009-12-21 10:24:44

... and the NULL always sort together at the end in accordance with standard Jet 4.0 collation settings: see http://msdn.microsoft.com/en-us/library/aa140022%28office.10%29.aspx : "NULL Collation Order : A Long value (read-only) that specifies where Null values are collated (sorted). For the Microsoft Jet provider, the value is always 4, which indicates that null values are sorted at the low end of the list."

onedaywhen 2009-12-21 10:25:43

...so the set of a) positive values and zeros, b) negative values, c) NULL values all sort together and is 100% predictable, the only problem being that the set of negative values appears in the wrong place. This was never a problem for me because I preferred to sort my ADO classic recordsets in preference to using an ORDER BY clause and anyhow I don't recall ever requiring negative ordering on a set of `DECIMAL` set that included negative values.

onedaywhen 2009-12-21 10:30:18

...the workaround using a ORDER BY clause is very simple e.g. `ORDER BY (my_dec_col < 0), my_dec_col DESC;` and when tested doesn't degrade performance significantly, nor does casting/coercing the values to `FLOAT` (Double). What he doesn't mention is that the `CDEC` (cast to `DECIMAL`) function has always broken for Jet/ACE SQL, which is appalling.

onedaywhen 2009-12-21 10:34:31

...But did you realize that since Jet 4.0 many users are using the `DECIMAL` data type without realizing it? Operating on a value using a decimal literal such as 0.5 in Jet/ACE SQL code will cause the result to be coerced to `DECIMAL` e.g. test this: `SELECT DISTINCT TYPENAME(CDBL(0.5) + 0.5 FROM Customers;` returns 'Decimal'. So explicitly avoiding the DECIMAL type requires care.

onedaywhen 2009-12-21 10:40:16

...But is it worth avoiding? If the business requires a data element with five decimal places, what do *you* do? Use and integer column and roll your own decimal scaling? Tell they they can only have four and use `CURRENCY`? Port to a more capable engine ;) ?

onedaywhen 2009-12-21 10:42:35

If you are ever in contact with Allen Browne, could you ask him to do a proper review of that article? Not only is it now out of date, it was erroneous and biased to begin with. Perhaps you could provide him with a peer review. I've sent a few emails to him over the years and, although I've find him genuinely helpful and gracious I've never got a response on this matter and the article remains unchanged. But yourself and others continue to reference it!

onedaywhen 2009-12-21 10:45:18

My response to your "helpful" comments is not something that is appropriate to post here.

David-W-Fenton 2009-12-21 23:33:58

Allen Browne has far more credibility in all things Access/Jet than you do, and always will. You don't develop in Access and seem interested only in the database engine. That point of view only slightly overlaps with the vast majority of Access users and makes much of what you write, erm, less than helpful.

David-W-Fenton 2009-12-21 23:36:51

"Allen Browne has far more credibility in all things Access/Jet than you do, and always will" -- meaning he can post factually incorrect information which folk propagate without questioning it themselves. Agreed, it's not a problem I can solve but that doesn't mean it isn't a problem.

onedaywhen 2009-12-22 08:52:58

"My response to your 'helpful' comments is not something that is appropriate to post here" -- I can imagine. jamieuka commerical_at googlemail period com

onedaywhen 2009-12-22 08:54:25

I tried changing default printer to no avail. The only printer on there now is a shared printer to a system that prints the picking slip correctly on the stand alone machine.

It may be possible to access the source after all. Can you think of any reason why it might work in a stand alone environment (on xp) and not in TS 2003? Thanks for any insight.

Daniel Payne 2009-12-18 13:43:36

Apply a fixed format to the field on the report, then, and you shouldn't have the problem. This doesn't really sound like the kind of printer issue I've encountered, which usually has a different set of symptoms (e.g., the field prints as entirely blank on problem printer drivers).

David-W-Fenton 2009-12-19 04:29:32

ansaurus

tags:

views:

answers:

Printing an Access report and decimal place weirdness

related questions