Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 12:18 AM   #1
Lochwood
Newly Registered User
 
Join Date: Jun 2017
Posts: 83
Thanks: 16
Thanked 2 Times in 2 Posts
Lochwood is on a distinguished road
add a Percentage to a value

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.

Lochwood is offline   Reply With Quote
Old 06-11-2019, 12:46 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,893
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: add a Percentage to a value

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 06-11-2019 at 12:54 AM. Reason: Corrected error in calculation
isladogs is offline   Reply With Quote
Old 06-11-2019, 12:50 AM   #3
Lochwood
Newly Registered User
 
Join Date: Jun 2017
Posts: 83
Thanks: 16
Thanked 2 Times in 2 Posts
Lochwood is on a distinguished road
Re: add a Percentage to a value

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.

Lochwood is offline   Reply With Quote
Old 06-11-2019, 12:57 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,893
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: add a Percentage to a value

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-11-2019, 01:47 AM   #5
Lochwood
Newly Registered User
 
Join Date: Jun 2017
Posts: 83
Thanks: 16
Thanked 2 Times in 2 Posts
Lochwood is on a distinguished road
Re: add a Percentage to a value

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))
Lochwood is offline   Reply With Quote
Old 06-11-2019, 02:12 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: add a Percentage to a value

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)
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-11-2019, 04:49 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,893
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: add a Percentage to a value

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-11-2019, 05:23 AM   #8
Lochwood
Newly Registered User
 
Join Date: Jun 2017
Posts: 83
Thanks: 16
Thanked 2 Times in 2 Posts
Lochwood is on a distinguished road
Re: add a Percentage to a value

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.
Lochwood is offline   Reply With Quote
Old 06-11-2019, 05:29 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,893
Thanks: 114
Thanked 2,979 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: add a Percentage to a value

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
% Percentage access2010 Reports 4 07-29-2013 11:04 PM
Percentage Jones Stanwell General 0 08-22-2011 04:22 PM
Trying to get a Percentage Windsor302 General 26 08-02-2011 01:38 PM
Percentage in SQL kirsten Queries 2 09-06-2010 02:12 AM
Trying to do a percentage The Rev Queries 2 01-23-2003 10:43 AM




All times are GMT -8. The time now is 07:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World