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.
add a Percentage to a value (1 Viewer)
- Thread starter Lochwood
- Start date
isladogs
MVP / VIP
- Local time
- Today, 10:37
- Joined
- Jan 14, 2017
- Messages
- 18,207
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
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
Last edited:
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))
Round([Standard_Rate]*(1+Nz(0.01*[Forms![Rates_Templates_Mainform]![Rates_Templates].[Form]![Percentage],0),2))
- Local time
- Today, 10:37
- Joined
- Sep 12, 2006
- Messages
- 15,634
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)
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
MVP / VIP
- Local time
- Today, 10:37
- Joined
- Jan 14, 2017
- Messages
- 18,207
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
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
