CRYSTAL REPORTS

[GaryB]

Hi,

I'm not sure if this is the right forum to post this, but, since Crystal reports can tie in to Access so well I thought there might be a better chance of more information here. I have an invoice report that is " almost every time, but not quite" a penny off. Since it is in an accounting area, accuracy is, of course, essential. The formula in the report reads as such:
IF {@DISCOUNT %} > 0 THEN {HEADER.SubTotalCost}/{@DISCOUNT %}*100 ELSE {HEADER.SubTotalCost}

what this is doing is adding up the individual items in the invoice and bringing them to a subtotaled amount before shipping and tax and that's where it ends up off by a penny.

Any ideas would be most welcome

Thanks

Gary

[Brandtrock]

Is it always off the same direction? Or is it over sometimes and under others?

In the CR Formula Editor, there are a number of functions available to use in your formulas, have you exhausted these?

What version of CR are you using?

Regards,

[GaryB]

Hi Brandtrock,

It seems vary either way. I've tried most of the functions I think that would work and so far nothing. Do you have a particular function in mind?

Thanks

Gary

[Brandtrock]

Sorry for the delay, I'm at the library with my boys and they distracted me so my answer timed out!!!! After logging back in, I'm trying again.

[Brandtrock]

Where in your report is the formula you referenced earlier?

Group header/footer, details, etc.

Is the calculation being performed on the sum of the invoice amounts different than the sum of the discount being applied to the individual line items and then summed?

Not sure that that is as clear as I want it to be.

Regards,

[GaryB]

group footer. Kind of got what you mean. Actually the discount occurs after the sum total. So we have sum total, discount % and amount, sum after discount then freight and taxes.

Gary

[Brandtrock]

I'll have a look when I get back home later tonight and see if I can get a formula/solution that works.

Regards,

[GaryB]

That is very kind of you.

Thanks

Gary

[Brandtrock]

Sorry it took so long to get back to a computer that is connected to the net. The library was closed Friday for Veteran's Day (BTW, :clap2: to all who have served) so I wasn't able to get online.

Have you tried using the Truncate formula? This will clear up anything past the number of places argument; which in effect always rounds down.

For example,

12.03
12.53
12.93

all result in 12 being returned.

in the case of truncating to the hundredths place,

12.031
12.035
12.039

would all result in 12.03 being returned.

The Crystal syntax for this function is Truncate(x, #ofplaces)

If this doesn't do it for you, or you need additional help in getting the Truncate function placed where you need it, you can PM me for an e-mail addy.

I will not be online again until Tuesday morning though. Sorry for the delay.

Regards,

[GaryB]

Hi Brandtrock,

I double checked the settings and the decimals setting is set @ 1.00. I did notice that the rounding setting is set at .01. I wonder if rounding might be the problem with this?
Thanks again

Gary

[Brandtrock]

Do you get the same bad results if you set the rounding differently?

[GaryB]

Yes, the same problem occurs. Here's the part that's driving me nuts. Let's say I have a total of $50.00 and the tax rate should be 8.25%. I print the invoice and get a tax rate of 8.24%. Then I'll reset the pricing for $49.95 and it will give me the correct tax %, but, I can have a price of $100.00 and the tax rate will be fine. It just doesn't make any sense to me at all. At first I thought the amounts ending in .00 might be the problem, but, the $50 vs $100 scenario seems to negate that idea.

I appreciate the help, but, I think we might be chasing something that we will never catch. The program generating the information for the invoice is not one I can change anything in. It was purchased from another company and, obviously, they have everything encrypted.

Thanks

Gary