Access 2003, percentage between two fields

If SellPrice was 100.00 and Cost was 50.00, the Profit would be 50%. No need to show trailing decimals... rounded to the nearest will be fine.

initial Investment (or cost): 50.00
SellPrice: 100.00
Profit:  100.00 - 50.00 = 50.00

% of profit = Profit / initial investment = 50.00 / 50.00 = 100% not 50%

Is this acceptable?

Select SellPrice, Cost, PercentProfit: 100*(SellPrice - Cost)/(Cost) from MyTable

Use Format to display the profit as a percentage:

    =Format((SellPrice - Cost) / SellPrice, "0%")

/gustav

Mike, trying yours now but it gets me whacky results. Will post a screenshot...

no time for image, going into meetinmg. Your formula is returning my SellPrice with 8 digits and then 2 trailing didgits

borrowing the format from Gustav's solution, try:

Select SellPrice, Cost, PercentProfit: Format((SellPrice - Cost)/(Cost), "0%") from MyTable

Tried that also, same results just different decimal places. Screenshot of first query:

There is no way you can obtain those results using the simple formulas posted here.
The Profit should be 24% and 37% respectively.

So correct your SQL to something like this:

Select *, Format((SellPrice - Cost) / SellPrice, "0%") As Profit
From YourTable

Select allOpen in new window

If you need the numeric value of the profit, use:

Select *, (SellPrice - Cost) / SellPrice As Profit
From YourTable

Select allOpen in new window

and set the Format property of the field Profit to: 0%

and/or post your buggy SQL here.

/gustav

gustav,

I think we're close but I'm trying to update a field, not just select and view. Would that change anything in what you've written? I mean, would it still work? Because it isnt when I try to convert your select to an update. The name of the field to update is ProfitPercentage, and I can set it as percent, long int, whatever you think it needs to be.

Damn. I tried both those but now I'm just getting either 1's or 0's, even a few -1's.

0/1 could be possible if your field is integer. -1 is not possible except if you have wigged data.

Perhaps you should upload a sample of your data and query.

/gustav

So what should the field size and format be? Maybe thats where Im screwing up, having the wrong combo of both those.

Currency. Always Currency for amounts.

/gustav

That definitely helped, thanks. Now it works, but the numbers dont match what you said they should be, see screenshot. First number is SellPrice, second number is Cost, third number is ProfitPercentage.

Great! Your results are correct.
The previous of mine must have been mixed up somehow.

/gustav

Nice!!! Thanks very much Gustav.

You are welcome!

/gustav