add a Percentage to a value

[Lochwood]

I am looking for the correct syntax in my query to add a percentage to a value. so i have a [Standard_Rate] in a table and i would like to have a column showing the percentage increase based on a field in a form. so if i insert 2 into the field, the query will return the value + 2%. i can manually get this by typing Expr1: Round([Standard_Rate]1.2,2) but i would like the user to enter this value into a form to see the increase.

 

[isladogs]

I assume you mean based on a form control e.g. a textbox txtChange.
On the form, the new value would then be shown in another textbox with control source Round([Standard_Rate]*(1+0.01*Me.txtChange),2)

However you may need to manage where the user leaves txtChange blank.
If so, use Round([Standard_Rate]*(1+Nz(0.01*Me.txtChange,0),2)

In a query, use Round([Standard_Rate]*(1+Nz(0.01*Forms!YourFormName.txtChange,0),2)

Note that the above doesn't actually change the Standard Rate. To do so, use an update query

 

[Lochwood]

Thanks, yes it will be a form control text box and an update query so they can input the percentage in the txt box and click a button to run the update query and change the values.

 

[isladogs]

Ah you didn't make that clear.
In the update query use
Round([Standard_Rate]*(1+Nz(0.01*Forms!YourFormName.txtChange,0),2) in the Update To line.

NOTE I've edited my original reply to add *0.01 to each expression

 

[Lochwood]

I am getting Syntax error comma in query expression. here is my line in the updateline of query. cant seen to see what would be wrong with this.

Round([Standard_Rate]*(1+Nz(0.01*[Forms![Rates_Templates_Mainform]![Rates_Templates].[Form]![Percentage],0),2))

 

[gemma-the-husky]

I would either store the percentage in a variable, and read it with a function, or put in a tempvars.


Then your expression becomes either of these, assuming you are storing the percentage increase as a double - so 2% as 0.02. Much easier than form expressions, and more re-usable I tihnk.

standardrate * (1+increaserate() )
standardrate * (1+tempvars.increase)

 

[isladogs]

You hadn't mentioned the control was on a subform.
Is the form/subform open when you are running the update query?
If so, is it being run from a button in the main form or from the subform itself?

If the form isn't open, you definitely need to do one of GTH's suggestions. Even if its open, its still a good idea

 

[Lochwood]

the control is on the subform with the button also being on the subform. i am a novice on access and GTHs last comment doesn't make make any sense to me.

 

[isladogs]

As the button is also on the subform, you shouldn't need to reference the main form. Just use the syntax I originally provided.

If it still fails, use a variable and function.