problem with formulas

1. Member
Join Date
Dec 1969
Posts
51

## problem with formulas

Hi can someone please slap me with a wet fish or just offer some advice.<BR><BR>i am trying to run a page that calculates a discount from a price then adds 10% tax to the subtotal.<BR><BR>price = 45<BR>discount of 15% = 6.75<BR>subtotal = 38.25<BR>tax of 10% of 38.25 = 3.825<BR>grand total (subtotal + tax) = 42.078<BR><BR>Thats how it should work. My problem is when I run the discount part of it, the amount comes up as 6,75 not 6.75 <BR><BR>I am unaware why this happens and how to fix. This also happens with the tax part of it.<BR><BR>Any ideas appreciated, fish are optional.<BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
2,031

## RE: problem with formulas

price = 45<BR>discount = CDbl(price * 0.15)<BR>subtotal = (price + discount)<BR>tax = 1.10<BR>grandtotal = (tax * subtotal)<BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## What country is your server in?

6,75 in Germany, for example, *IS* the same as 6.75 in the USA.<BR><BR>Different countries have different standards for what a decimal point looks like, etc., etc.<BR><BR>In other words, I bet your code is working perfectly and it&#039;s only the display that is off.<BR><BR>SO...<BR><BR>What country do you *want* this to work for? <BR><BR>Put in <BR><BR>&#060;%<BR>Session.LCID = nnnn<BR>%&#062;<BR>where you get the nnnn from this table:<BR>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsmsclcid.asp<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## This one will be fun...

...I wonder which of us diagnosed his bug correctly.<BR><BR>I am *not* giving odds. I figure chances are even.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
2,031

## LOL.

I just thought his formula could use a little reworking, since it was part mathematical and part English ;) I&#039;ll put my money on you. :)

6. Member
Join Date
Dec 1969
Posts
51

## RE: What country is your server in?

Hey Bill and Kristin,<BR><BR>I am in Australia. The formula does work fine on the page. (I editted it so as not to bore you with all the details) When I try to insert the sql into the table it thinks there is a mismatch in fields due to the commas. Below is the sql of what is is trying to do.<BR><BR>INSERT INTO table (username,thedate,thecost,gstamount,status) Values (&#039;billw&#039;,&#039;7/2/2002&#039;,&#039;42,075&#039;,&#039;3,825&#039;,&# 039;Due&#039;)<BR><BR>It wants to store the values of 42,075 and 3,825 in the table as 42075 and 3825.<BR><BR>Perhaps my table settings need refinement.<BR>Database is access. These two columns are the same settings, Currency, 2 decimal places.<BR><BR>Thanks again.

7. Senior Member
Join Date
Dec 1969
Posts
2,031

## Try...

changing the access datatype to a number<BR>then use CDbl to convert the values to a decimal.<BR>I can&#039;t see why that wouldn&#039;t work. Unless Bill has a better plan.<BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Access is stupid...

...it does *NOT* understand the use of , as a decimal point.<BR><BR>It only understands USA standard numbers and dates.<BR><BR>So when you generate your SQL statement, do this:<BR><BR>&#060;%<BR>... do the calcs ...<BR>saveLCID = Session.LCID<BR>Session.LCID = &h0409 &#039; USA<BR>SQL = "INSERT INTO table (username,thedate,thecost,gstamount,status) " _<BR>&nbsp; &nbsp; & "Values (&#039;" & username & "&#039;,#" & theDate & "#," _<BR>&nbsp; &nbsp; & theCode & "," & gstAmount & ",&#039;" & stat & "&#039;)"<BR><BR>Session.LCID = saveLCID<BR>%&#062;<BR><BR>See that? You use the USA locale just for the time needed to create the SQL statement string.<BR><BR>Things to note:<BR><BR>(1) Kristin is 100% right! You should *NOT* put &#039;...&#039; around numeric data. Period.<BR><BR>(2) For a date/time field (thedate in your table) you must put #...# around the literal value *INSTEAD* of &#039;...&#039;. Yeah, I know. Every other DB in the world uses &#039;...&#039;. Live with it. Or get a different DB.<BR><BR>Incidentally, there really isn&#039;t any point in using a datatype of Currency for a numeric field in the DB *unless* you will be using Access to write reports for you. If all your work will be done via ASP, then you might as well use a DOUBLE numeric type, since VBS is going to treat all numbers it gets from the DB as just plain old numbers anyway (that is, VBS will ignore your "2 decimal places" setting...you&#039;ll have to use FormatNumber or FormatCurrency anyway).<BR><BR><BR><BR>

9. Member
Join Date
Dec 1969
Posts
51

## RE: Access is stupid...

Thank for the advice. Your a true gentleman Bill.<BR><BR>Yeah I know of the &#039; &#039; around the numeric data. Thats a leftover from stopping the two many fields error I was getting early on in the piece.<BR><BR>Hmm ok. Thats fixed the number fields, unfortunetely the dates are going in the wrong way. Anyways I can change the database and fix that later.<BR><BR>Displaying these fields on a page using formatcurrency(thecost,2) displays the following....<BR><BR>R\$ 49,50 why the R ?

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ummm...where is the server?

R\$ is, I think, the symbol used in South Africa for Rands.<BR><BR>By default, you are going to get output that matches the system locale of the server, however the server was set up.<BR><BR>But you can force it to your own locale! Just do<BR><BR>Session.LCID = nnnn<BR><BR>in your Session_onStart in GLOBAL.ASA (or in any page where you process numbers and dates, for that matter), where nnnn is the value for Australia.<BR><BR>******************<BR><BR>As for the date fields:<BR><BR>If you previously put data into Access *without* changing the LCID, then you are already hosed.<BR><BR>Consider: You gave Access 7/2/2002 and Access, since it *only* accepts USA dates, saw that as 2 July 2002!!!<BR><BR>Dates that you *now* put into the DB *should* be going in right. But remember, if you look at the dates within Access, you *might* still be seeing USA standard. Ask to see a long form (with the Month spelled out) to be sure, when looking at data inside Access.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•