|
| |
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
 |
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
|
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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?
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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::"
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 6335
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 6335
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
%>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 6335
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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"))
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 296
From: Louisville KY USA
Status: offline
|
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
Posts: 6335
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
%>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
jonance
Posts: 296
From: Louisville KY USA
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
Are they text fields? 
