Round currency field to 2 decimal places (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

jonance -> Round currency field to 2 decimal places (3/22/2004 18:30:41)

Hi,

I have a problem that i have run into with access and my update functions of my site. To start, i have a currency field in access...it is fixed at 2 decimal places. When i view the database (both in access and on the site when the info is displayed)...all you see are two decimal places. However, when i click on a price in the database, two more decimal places are revealed.

So in essence...what appears to be, say...$35.67 is actually $35.8745. My problem with this is that i have an update function which allows the user to do a search and replace for a price in the database. But when he enters 35.67 (which is what he sees)..it doesn't replace that price because the number is actually 35.8745. I need to fix this asap, and looked online to find out the best thing to do but have found nothing valuable. What is the easiest way to correct this problem? I tried converting to a number instead of currency and it lost all of the info after the "." and turned it all to ".00". Anybody know how i can actually truncate the numbers to 2 decimal places for good?

Thanks in advance.
John



rdouglass -> RE: Round currency field to 2 decimal places (3/23/2004 8:13:14)

Have you tried using the FormatNumber function before you write to the DB? Something like:

myField = FormatNumber(myField,2)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2mic.asp



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 10:23:06)

Where would i put that? Right now i have an update query in the DRW. The problem seems to be that the numbers already have 4 decimal places, and the search part of the search and replace function isn't matching them up. WOuld this prevent that?

Would changing from currency to number get rid of this problem?



rdouglass -> RE: Round currency field to 2 decimal places (3/23/2004 10:30:12)

quote:

The problem seems to be that the numbers already have 4 decimal places, and the search part of the search and replace function isn't matching them up


Search and replace? [:o] Are they text fields? [8|]

If they are number fields, can you post the SQL you're now using and clarify what field(s) you want rounded?

Are the numbers you want to work with coming from a db or a form?

[&:]



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 10:46:14)

I'll try to clarify...here is the sql for the update function

<%
fp_sQry="UPDATE products SET price=::newprice:: WHERE price=::oldprice::"
fp_sDefault="newprice=&oldprice="
fp_sNoRecords="No records returned."
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


I have a form where the client enters an amount in the field 'oldprice'. This is the price that is already in the database, and should be replaced by the amount he enters in another field called 'newprice' in the same form. This form posts to an asp page with the above sql update function in a DRW.

The problem is that in the database, in the price field (which is in the currency format), what appears at first glance to be, say $36.34 on the site is actually $36.3421 (4 decimals) in the database. So when he enters 36.34 in the 'oldprice' field, and whatever he wants the new price to be....it doesn't replace it because it is not actually a match. He would need to type in 36.3421 (which i verified works). But on the site, he doesn't see those numbers, why would he, it is currency and there are only two decimals.

What i need to do is truncate all of the prices that are ALREADY in the database to 2 decimal points, and drop the extra two. I have the currency field set to 2 decimals...but apparenty that only is the display and it still saves as 4 decimals...b/c when i click on the actual number in the DB, all for decimals are reveals, hence the problem.

So i'd like to know the easiest way for me to do this with existing records and ensure any new records will be okay as well....should i change the format from currency, and if so...to what?

Thanks,
John



rdouglass -> RE: Round currency field to 2 decimal places (3/23/2004 10:58:56)

Maybe something like:

fp_sQry="UPDATE products SET price=::newprice:: WHERE Round([price],2)=::oldprice::"



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 11:11:46)

Okay,

I think this is getting very close. The only problem i have now is that this wont work for all the records...just some.

The reason is that the Round function seems to sometimes make the price round up, thus not matching what is shown on the site. Is there a way to just truncate the price to 2 decimals instead of rounding...that way the two decimals that are shown on the site will be exactly the ones that my update sql is searching for?

Thanks,
John



rdouglass -> RE: Round currency field to 2 decimal places (3/23/2004 11:24:30)

quote:

fp_sQry="UPDATE products SET price=::newprice:: WHERE Round([price],2)=::oldprice::"


fp_sQry="UPDATE products SET price=::newprice:: WHERE FormatNumber([price],2)=::oldprice::"

That any better?



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 12:16:18)

I got this error when i put in that code

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'FormatNumber' in expression.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

Any ideas? Thanks alot for all your help!

John



rdouglass -> RE: Round currency field to 2 decimal places (3/23/2004 14:08:35)

quote:

fp_sQry="UPDATE products SET price=::newprice:: WHERE FormatNumber([price],2)=::oldprice::"


fp_sQry="UPDATE products SET price=::newprice:: WHERE (((FormatNumber([price],2))='::oldprice::'))"

That one any better? If not, I'm not sure what it's missing. I do know that you can use that function in queries; I use 'em like that all the time.

Here's one I used before in an ASP page and still works fine:

"SELECT * FROM tblOrders WHERE (((FormatNumber([TotalLabor],2))>'100'))"

and that DOES work. Note however, I treated "oldprice" as text and not a number field.



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 14:27:07)

That gave me the same error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'FormatNumber' in expression.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

Not sure why either....what are my other options?



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 14:51:45)

Just another thought....if i can make the database round the numbers itself, then the round function you sent earlier should work....is there a way to do that?



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 15:15:10)

If i were to just want to give up on leaving the currency field, and change to an integer....what would give me currency-like results in access....everything i tried set the decimals to .00

Thanks,
John



BeTheBall -> RE: Round currency field to 2 decimal places (3/23/2004 15:58:43)

No you can't use integer. Integers are whole numbers. I haven't answered your email on this because I have been struggling to find a solution. I too thought of using FormatNumber, but apparently that function cannot be used within a SQL statement. Here is what I have come up with. I am not at home so can't test it on your DB, but it may work.

Try inserting one line above your SQL statement. On that line enter this:

MyLen=Len(Request.Form("oldprice"))

Then change your SQL to this:

fp_sQry="UPDATE products SET price=::newprice:: WHERE LEFT([price],"&MyLen&") ='::oldprice::'))"

The first bit of code will capture the length in characters of the value you want to change and then the SQL will look for a matching price of that many characters. This is probably very crude, but may work.



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 16:19:20)

Thanks...Where do i put the new line of code? I put it in the DRW right above the Update line..but it wouldn't take....see below:

Here is the sql i put in the DRW:

MyLen=Len(Request.Form("oldprice"))
UPDATE products
SET price=::newprice::
WHERE LEFT([price],"&MyLen&") ='::oldprice::'))

I got this error when i tried to click 'OK'..it said the query contains errors:



Server error: Unable to retrieve schema information from the query:

MyLen=Len(Request.Form("oldprice"))

UPDATE products

SET price=1

WHERE LEFT([price],"&MyLen&") ='2'))

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/louisvilleguide.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)



BeTheBall -> RE: Round currency field to 2 decimal places (3/23/2004 16:33:24)

Do it in HTML view, not via the wizard. Also, if you haven't applied the Spooky Diet mentioned referenced in my signature, please do that first. Then change:

<%
fp_sQry="UPDATE products SET price=::newprice:: WHERE price=::oldprice::"
fp_sDefault="newprice=&oldprice="
fp_sNoRecords="No records returned."
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

TO

<%
MyLen=Request.Form("oldprice")
fp_sQry="UPDATE products SET [price]=::newprice:: WHERE Left([price],"&MyLen&")=::oldprice::"
fp_sDefault="newprice=&oldprice="
fp_sNoRecords="No records returned."
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 16:42:12)

I applied that code (and the diet)...i didn't get an error, but the price didn't change either....



BeTheBall -> RE: Round currency field to 2 decimal places (3/23/2004 16:58:27)

Bad cut and paste.

Change:

MyLen=Request.Form("oldprice")

to

MyLen=Len(Request.Form("oldprice"))



jonance -> RE: Round currency field to 2 decimal places (3/23/2004 20:18:31)

Still didn't change even after the new one....here is my sql updated...anybody have any other ideas?

<%
MyLen=Len(Request.Form("oldprice"))
fp_sQry="UPDATE products SET [price]=::newprice:: WHERE Left([price],"&MyLen&")=::oldprice::"
fp_sDefault="newprice=&oldprice="
fp_sNoRecords="No records returned."
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



jonance -> RE: Round currency field to 2 decimal places (3/24/2004 14:03:13)

Okay...i'm gonna try to nip this in the bud where i think it begins. The reason that the prices are getting 4 decimals is because of another script that allows the client to raise or lower all prices by a certain percentage. This is resulting in these strange #'s. Now if i can use the Round() function mentioned in an earlier post to round those numbers off as that change is made, everything would be fine as the update would only save the two decimals. Only problems is that i am not real sure where to input the round() function in the below sql statement. Can anyone tell me?

Here is the sql for the price increase by a certain percentage (which is sent from a form):

<%
fp_sQry="UPDATE products SET price=price + price * '::increase::' "
fp_sDefault="increase="
fp_sNoRecords="No records returned."
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



BeTheBall -> RE: Round currency field to 2 decimal places (3/24/2004 15:00:52)

Probably like below.

<%
fp_sQry="UPDATE products SET price=Round(price + price * '::increase::',2) "
fp_sDefault="increase="
fp_sNoRecords="No records returned."
fp_sDataConn="louisvilleguide"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



jonance -> RE: Round currency field to 2 decimal places (3/24/2004 15:15:14)

Works PERFECT! All the 4 decimal numbers are gone, so now the search/replace matches perfectly and updates fine.

Thanks for everyones help!

John



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625