Update query - calcualte a percentage input by user? (1 Viewer)

oZone · Oct 17, 2008

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!

MSAccessRookie · Oct 17, 2008

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!

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

Code:

OldPrice+OldPrice*[Enter a Percentage:]

Brianwarnock · Oct 17, 2008

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

if you want the user to enter 10 for 10%

Brian

MSAccessRookie · Oct 17, 2008

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

if you want the user to enter 10 for 10%

Brian

It would depend on whether a 6% increase would be entered as 6 or 0.06. oZone will need to decide that.

Brianwarnock · Oct 17, 2008

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.

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

Brian

MSAccessRookie · Oct 17, 2008

Brianwarnock said:
The op specifically stated that it would be 6

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

Brian

Good catch

oZone · Oct 17, 2008

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

oZone · Oct 17, 2008

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?

Brianwarnock · Oct 18, 2008

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

oZone · Oct 18, 2008

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?

Brianwarnock · Oct 18, 2008

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

oZone · Oct 19, 2008

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!

Brianwarnock · Oct 19, 2008

You really do need to drive this from a form.

Brian

oZone · Oct 19, 2008

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?

Brianwarnock · Oct 19, 2008

You can do a certain amount as I illustrated but nesting IIFs is clumsy and slow and limited.

Brian

gemma-the-husky · Oct 19, 2008

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

oZone · Oct 20, 2008

I see, thanks for all the help.

Brianwarnock · Oct 20, 2008

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

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

MSAccessRookie · Oct 20, 2008

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?

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.

oZone · Oct 20, 2008

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.

Update query - calcualte a percentage input by user? (1 Viewer)

Registered User.

AWF VIP

Retired

AWF VIP

Retired

AWF VIP

Registered User.

Registered User.

Retired

Registered User.

Retired

Registered User.

Retired

Registered User.

Retired

Super Moderator

Registered User.

Retired

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread