Fraction of a dollar shows zero balance incorrectly!! Clng problem? 1

[Nelz]

I have a complex database that in one area calculates an order and figures the sales tax if applicable. In this case the 7.75% tax is calculated, and a total is shown.

Apparently the customer paid the total, and there is shown a zero balance, but they are still showing up on a recievables aging report because they still owe 0.0025 or 0.0033 etc. This only shows up when you click in the field on the underlying query. The report shows zero since it is in currency format.

I used the following formula to calculate the tax =
[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]

Trouble is I also used [LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments]in the aging report and apparently these do different things.

I dont know which to set it to at this point. Although it only affected a small number of orders...its a pain.

I dont really understand the difference between the two ways of calculating I used as I took them from a couple of different examples.

I also used this formula in my recievables aging report for the >90 day column:
91+ Days: Sum(IIf((Date()-[ShipDate])>90,[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0))

This works....but in my "BALANCE" column it wont let me do it that way....it only works like this:

Balance: Sum([LineTotal]*(1+[SalesTaxRate])+[FreightCharge]-nz([Total Payments]))

If I substitute the : [LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]

it says invalid use of null.

Can I mix them? Again my original form that calculates the sales tax and figures the total uses this for the sales tax field....=CLng([Order Subtotal]*[SalesTaxRate]*100)/100

This is the culprit I think. Should I change THAT to : [Order Subtotal]*(1+[SalesTaxRate]?

This is driving me nuts???? THANKS IN ADVANCE

 

[scriverb]

Try this in the Aging report:

CInt([LineTotal]*(1+[SalesTaxRate])*100)/100 +[FreightCharge]-nz([Total Payments]

This should eliminate the stragling fractions of a cent that are causing the aging report to show these records.

Bob Scriver

 

[Nelz]

Thanks...
I have already gone through my entire application and changed all the formulas to be the same....(the Clng version as opposed to the other...there were some of each before depending on when it was I built it (lol) ...

Anyway...my current solution was to put >0.009 or <-0.009 in the criteria of the final report....which eliminated those &quot;outlyers&quot; from being displayed....but I had thought about your solution too. do I need to do that EVERYWHERE I have currently used the Clng for calculating balances? ( I have balances for current, 30 days, 60 days, >90 days and totals on at least 5 different reports and 20 different querys. On all I have changed them now to the uniform clng.

What do you think? Also..is there a version of &quot;find and replace&quot; for an entire database? to change something like this?

Thanks again..I cant tell you how much I appreciate having someone to ask these kinds of questions too. I only post here as a last resort.

 

[scriverb]

I would think that you have to do it everywhere. You see the multiplication process carries the decimals out as far as they can. You must use this CIng to truncate off the digits that you don't want saved.

Thanks for the star and I was glad to be able to help you with your problem. Come back and see us more often.

Bob Scriver

 

[markphsd]

there is also the
round function

Round(Total, 2)

I use it for rounding off cents. It seems to work well.




Mark P.

Bleh

 

[beetee]

To go a bit further with markphsd's concept, why not create an update query to fix the data rather than fixing all those reports.

Obviously nobody is going to every owe .00035 cents; and those type of values may be mucking up other calculations.

Additionally, when the tax is calculated, it should be rounded to the nearest cent.

 

[Nelz]

Well, thanks to all. I guess I will clean up the data...that makes sense. I dont understand the concept of the Clng expression I guess. I took it from a sample database that used that formula to calculate sales tax..

What I should have done is stored the total somewhere....but I actually just recalculate it in every situation where I need it. If it was stored in a table I could have truncated the decimals.

I'll try the round function too!

 

[beetee]

I think you were correct NOT to store the tax, my apologies for assuming that is what was done.

That being the case, use the rounding function in your calculated fields.

 

[scriverb]

Use the Round Function if you are using ACCESS 2000+. The Round Function is not available for ACCESS 97. ACCESS 97 requires you to be a little more creative in manipulating the decimal characters. CLng, CInt, Fix, Int functions are all useful in working with double precision numbers and manipulating the decimal values.

I didn't know what ACCESS version you were using. We can give you an explaination of the CLng function and how it is used to perform the thousands field stripping demonstrated above.

Bob Scriver

 

[Nelz]

I am using Access 2000. Any ideas about a &quot;find and replace&quot; throughout the entire database? Can you do that within the modules or something?

Thanks again.

 

[scriverb]

Just open your code module and the Edit menu has a Replace selection that allows for the Find and Replace with in current procedure, current module, or current database.

Bob Scriver

 

[MichaelRed]

but the global find / replace in Access is just for the modules, and does not find (or replace) expressions in queries or controls.

There ARE third party tools which do search through the entire db (all object types) and do a search and replace. Some are commercial (speedferret) while others are either public domain. or shareware (sorry I do not recall the names of the public domain or shareware items). Use the &quot;advanced&quot; search in htese fora using key word speed ferret (or speedferret?) and you should find references to several items of interest.

In GENERAL, I would advise that you &quot;fix&quot; the data and then reconsider at least a part of your design stratgey. ONE significant element in this case would be to isolate / synchronize the approach to a common calculation - e.g. generate a module / procedure which does which ever calculation you select and then ALWAYS ues the procesure to calculate the value. Further, for each STORED value it must be changes to the same data TYPE (i.e. currency), so the procedure should have the same retur TYPE as the stoeed value.

I aNOT trying to belittle you, but it 'looks like' you are in way over the level of expertise necessary for the responsability you have been assigned. I would suggest that you get some &quot;help&quot; on a professional basis. Third party turorials and references can help, but mistakes in the area of billing and invoicing can be &quot;carrear impacts&quot;, so I would recommend enrollment in formal courses and a discussion with your supervisor. the latter is partly to see if the organization is willing to provide the course work, or third party refernence material and partly to advise them that there are issues with the application as it is currently implemented. The latter should help you in any potential future 'carrear&quot; discussions and enlist their help in carefully reviewing the output documents.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places