I can't seem to get this simple formula working when using a percentage field in the calculation (1 Viewer)

[Lateral]

Hi guys

I have a table that contains a list of Parts in a stock and inventory type of database.

It has field called "[Markup]" that contains the percentage markup and is calculated and updated each time the database is start via a query. The [Markup] field is defined as a Percentage field type. All of this works well.

The following is the query that updates the [Markup] field:

([UnitPrice]-([Wholesale]))/[Wholesale])


I have a form that displays the records in the Parts table in Datasheet view and want to calculate a new "UnitPrice" because the Wholesale price ([Wholesale]) has changed and I want to maintain the same Markup percentage.

I have spent hours trying to work out a VBA formula that I can use via the form but just can't get it!

For example, if the new Wholesale is $9.88 and the Markup percentage is 26.56% then the following formula on a calculator works fine:

9.88 + 26.56% = 12.50

I thought I could simply do the following in VBA:

Me.txtUnitPrice = Me.Wholesale * Me.Markup

but the result is $2.62

What am I doing wrong???

Thanks for any help you can provide.

Cheers
Greg

 

[theDBguy]

Hi Greg. Try it this way:

Me.txtUnitPrice = Me.Wholesale * (1 + Me.Markup)

 

[Lateral]

You're a legend DBGuy!

It works like a treat.

Cheers
Greg

 

[theDBguy]

You're a legend DBGuy!

It works like a treat.

Cheers
Greg

Hi Greg. Glad to hear it worked for you. Good luck with your project.

 

[Gasman]

Hi guys

I have a table that contains a list of Parts in a stock and inventory type of database.

It has field called "[Markup]" that contains the percentage markup and is calculated and updated each time the database is start via a query. The [Markup] field is defined as a Percentage field type. All of this works well.

The following is the query that updates the [Markup] field:

([UnitPrice]-([Wholesale]))/[Wholesale])


I have a form that displays the records in the Parts table in Datasheet view and want to calculate a new "UnitPrice" because the Wholesale price ([Wholesale]) has changed and I want to maintain the same Markup percentage.

I have spent hours trying to work out a VBA formula that I can use via the form but just can't get it!

For example, if the new Wholesale is $9.88 and the Markup percentage is 26.56% then the following formula on a calculator works fine:

9.88 + 26.56% = 12.50

I thought I could simply do the following in VBA:

Me.txtUnitPrice = Me.Wholesale * Me.Markup

but the result is $2.62

What am I doing wrong???

Thanks for any help you can provide.

Cheers
Greg

So that calculates your markup in $
Then you just add it to the wholesale amount. ?

 

[Micron]

What am I doing wrong?

You're thinking that vba expressions work like calculators. A calculator sees what you wrote 9.88 + 26.56%
and does this
9.88 + (9.88*26.56/100)
Going forward, you need to be literal with the math - at least to some degree. You could also write
9.88*1.2652
but then you'd be figuring out in your head that including 1 will "add" the original amount to the amount you get when multiplied by the decimal version of 26.52%

 

[Lateral]

Thanks for all of the help guys. I learn a lot from this forum.

 

[Lateral]

So that calculates your markup in $
Then you just add it to the wholesale amount. ?

Hi Gasman


I want to maintain and always have the same Markup when the cost price (wholesale) price changes ( usually increases) so this formula uses the existing Markup and new wholesale price to work out the new purchase price.