Update query - calcualte a percentage input by user?

O

oZone

Registered User.
Local time
Today, 13:43
Joined
Oct 17, 2008
Messages
103
Hi, I'm new to MS Access and have a question about Parameter queries.

I have a "Parts" database that contains part numbers, descriptions, and prices and I'm trying to create an Update Query that will prompt the user for a part number, then prompt them for a percentage to increase or decrease a part by, then update the part price in the Parts table. But I'm having some trouble with the math and getting Access to do what I want it to do...

I'm editing the query in design view and I've entered two fields in the query, "PartNumber" and "Price" from the Parts table. I know how to prompt the user to enter a value, by using brackets [ ], but unsure how to format the percentage calculation.

So far I have this in the "Update To" Price field:
[Price]*([Enter a percentage:]%)

But keep getting an error because I'm not doing something rite...

Here is an example of what I am trying to achieve: I'd like the query to prompt the user first for the part number, then for a percentage to increase or decrease the part price by.
The prompt should say "Please enter a percentage:" then the user would enter "10" to increase the price by 10% or "-10" to decrease the price by -10%. Then the query would multiply the part price by what the user entered and update the Part table accordingly.

I'd like the prompt to be simple, E.G. rather than requiring the user to enter "1.05" to increase by 5%, just have the user enter "5" for a 5% increase.


Any help would be greatly appreciated,
Thanks! :)
 
oZone said:
Hi, I'm new to MS Access and have a question about Parameter queries.

I have a "Parts" database that contains part numbers, descriptions, and prices and I'm trying to create an Update Query that will prompt the user for a part number, then prompt them for a percentage to increase or decrease a part by, then update the part price in the Parts table. But I'm having some trouble with the math and getting Access to do what I want it to do...

I'm editing the query in design view and I've entered two fields in the query, "PartNumber" and "Price" from the Parts table. I know how to prompt the user to enter a value, by using brackets [ ], but unsure how to format the percentage calculation.

So far I have this in the "Update To" Price field:
[Price]*([Enter a percentage:]%)

But keep getting an error because I'm not doing something rite...

Here is an example of what I am trying to achieve: I'd like the query to prompt the user first for the part number, then for a percentage to increase or decrease the part price by.
The prompt should say "Please enter a percentage:" then the user would enter "10" to increase the price by 10% or "-10" to decrease the price by -10%. Then the query would multiply the part price by what the user entered and update the Part table accordingly.

I'd like the prompt to be simple, E.G. rather than requiring the user to enter "1.05" to increase by 5%, just have the user enter "5" for a 5% increase.


Any help would be greatly appreciated,
Thanks! :)
Click to expand...


I think all you might need is to set the "Update To" Price field to the something like:

Code: 
OldPrice+OldPrice*[Enter a Percentage:]
 
wont that be
OldPrice+OldPrice*[Enter a Percentage:]/100

if you want the user to enter 10 for 10%

Brian
 
The op specifically stated that it would be 6

I'd like the prompt to be simple, E.G. rather than requiring the user to enter "1.05" to increase by 5%, just have the user enter "5" for a 5% increase.
Click to expand...

Don't get uptight you do a good job but we all slip up on the user requirements from time to time.

Brian
 
Thanks for the quick responses!

Thanks especially to Brian, your suggestion worked great :)

I removed the quotes and entered:
[Price]+[Price]*[Enter a Percentage:]/100


Thanks again ;)
 
One more question, sorry, forgot to ask earlier...

In the prompt I wanted to say something like "Please enter a percentage, E.G. 10% or -10%" so the user would realize they could just enter a negative number to decrease the price, but Access wont allow me to use a % sign in the message to the user. It errors out when I run the query.

How would one display a percentage sign in the message to the user? And, if the user enters "10%" instead of just "10" how would I make Access accept and calculate it?
 
Last edited:
If you require anything more than the simplest things then I would drive the query from a form. Then you can give explanatory Labels and back the input textboxes with VBA code to verify and edit the data.

Brian
 
I understand that forms may be better in this situation, but I am required to do this with a query.

Is there no way to do this with a query?
 
I don't know why you couldn't display a % sign I could
I ran a query with this field with no problem.

Expr1: [field3]+[field3]*(IIf(Right([Enter %],1)="%",Left([enter %],Len([enter %])-1),[enter %])/100)

Brian

PS Access will accept [Please enter a percentage E G 10% or -10%] as the prompt. punctuation is not permitted
 
Last edited:
Thanks for the help.

I found a minor flaw in this query, in that if I enter a non-numeric character when it asks the user to "Enter a percentage", it errors out.

Is there a way to set a validation rule in the query that will only allow numeric characters. If a non-numeric character is entered I'd like a message sent to the user such as "Error: Please enter integers only"

I know how to do this with a table, but how would you set a validation rule with a query? Can it be done?

Thanks! :)
 
I understand that a form would be the best solution, but I am a beginner to MS Access, still learning, and am curious if you can set validation rules for a parameter query... I assume you can't?
 
You can do a certain amount as I illustrated but nesting IIFs is clumsy and slow and limited.

Brian
 
i think after you get the input from the user, you should validate that it is an expected range, because of the presentation issues you describe
 
gemma-the-husky said:
i think after you get the input from the user, you should validate that it is an expected range, because of the presentation issues you describe
Click to expand...

I think that you will have to help me here Gemma, if you are just running a query how do you do that without a bunch of IIfs, especially as the user is permmitted to enter 5 or 5%.

Brian
 
oZone said:
I understand that a form would be the best solution, but I am a beginner to MS Access, still learning, and am curious if you can set validation rules for a parameter query... I assume you can't?
Click to expand...

As long as you are still learning, take time to learn about using forms as well. Brian is correct about forms being the way to go here, using either Macros (Easiest) or VB (Best) to control the actions. Forms are not too difficult to grasp, and once you have, a whole new world will be opened to you.
 
Yeah. Earlier I just wanted to know the limitations of queries E.G. what you could and couldnt do with them.

I bought an MS Access book and am now reading about forms and soon to be doing the practice exercises accompanied by those chapters. Looking forward to learning more about forms. :)
 
You must log in or register to reply here.

Similar threads

C
Subform Footer Textbox wont update
Replies
11
Views
271
Pat Hartman
P
C
Sub or function not defined
Replies
12
Views
449
Pat Hartman
P
MajP
The Microsoft Engine Does not Recognize... as a Valid Field Name in CrossTab
Replies
5
Views
307
Pat Hartman
P
W
Solved Duplicate Items in Query
Replies
5
Views
1,020
wmix
W
R
Update Query with a join to another query - Help
Replies
6
Views
260
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom