Microsoft Access: Divide two fields and put result in third field

  • Thread starter Thread starter billy4c2000
  • Start date Start date
B

billy4c2000

I am looking to divide two fields "GainOnSale" and "Principal" and input the
result to a third field "Premium" as a percentage with a decimal to the 100th
place. Also, I'm not sure where I should put the formula. Any help is
appreciated. Thanks.
 
Hi,


The maximum precision a table allows you is 28 digits with a Decimal
datatype. Sure, a Double datatype allows you up to 1E-300+, but with only 17
(+/-) digits of precision. To get 100 digits of precision, a possible
solution is to the arithmetic (+, -, *, /) on a user defined "structure",
which is not a free lunch, unless you find someone who has already do it in
VBA.



Hoping it may help,
Vanderghast, Access MVP
 
Here's an example query ...

SELECT tblTest.Num1, tblTest.Num2,
Format$([Num1]/[Num2],"#,##0.0000000000%") AS Expr1
FROM tblTest
WHERE (((tblTest.Num2)<>0));

I'm not sure what will happen if you try to increase the number of decimal
places to 100, but perhaps that was a typo?
 
Where do I put that query... exactly. Somewhere in the design view of the
table, in a query?? Below is what I've done from your query. Thanks.

SELECT tblSales.GainOnSale, tblSales.Principal
Format$([GainOnSale]/[Principal],"#0.00%") AS Premium
FROM tblSales
WHERE (((tblSales.Principal)<>0));

Brendan said:
Here's an example query ...

SELECT tblTest.Num1, tblTest.Num2,
Format$([Num1]/[Num2],"#,##0.0000000000%") AS Expr1
FROM tblTest
WHERE (((tblTest.Num2)<>0));

I'm not sure what will happen if you try to increase the number of decimal
places to 100, but perhaps that was a typo?
I am looking to divide two fields "GainOnSale" and "Principal" and input
the
result to a third field "Premium" as a percentage with a decimal to the
100th
place. Also, I'm not sure where I should put the formula. Any help is
appreciated. Thanks.
Click to expand...
Click to expand...
 
You're missing a comma after tblSales.Principal. Once you've added the
comma, try this ...

First copy the SQL text that I posted, with your modifications to the field
and table names - and not forgetting that comma - to the clipboard. Now from
the Insert menu, choose Query. In the New Query dialog box, accept the
default, Design View, and click the OK button. If Access displays a Show
Table dialog box, click the Close button. Now from the View menu select SQL
View. In SQL View, all of the text should be highlighted. Leave it that way,
and from the Edit menu choose Paste. This will paste in the SQL text from
the clipboard, replacing the highlighted text. From the View menu, select
Datasheet View to see the result.

From the View menu again, select Design View. This graphical design grid is
how you will probably create most of your queries. You rarely have to create
queries in Access by typing SQL statements like the one I posted. But you
will often get answers to questions in this form, because it is easily
pasted into a newsgroup post, so it is much faster and easier to answer
questions about queries this way than to attempt to describe the
step-by-step process of creating the same query using design view.

--
Brendan Reynolds
Access MVP

billy4c2000 said:
Where do I put that query... exactly. Somewhere in the design view of the
table, in a query?? Below is what I've done from your query. Thanks.

SELECT tblSales.GainOnSale, tblSales.Principal
Format$([GainOnSale]/[Principal],"#0.00%") AS Premium
FROM tblSales
WHERE (((tblSales.Principal)<>0));

Brendan said:
Here's an example query ...

SELECT tblTest.Num1, tblTest.Num2,
Format$([Num1]/[Num2],"#,##0.0000000000%") AS Expr1
FROM tblTest
WHERE (((tblTest.Num2)<>0));

I'm not sure what will happen if you try to increase the number of decimal
places to 100, but perhaps that was a typo?
I am looking to divide two fields "GainOnSale" and "Principal" and input
the
result to a third field "Premium" as a percentage with a decimal to the
100th
place. Also, I'm not sure where I should put the formula. Any help is
appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top