UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Queries/VBA/expression Builder, Access 2016    
 
   
sk5347
post Mar 13 2018, 04:07 PM
Post#1



Posts: 8
Joined: 23-January 15



Hello all,
I am using MS Access 2016, and I am a beginner.
I cannot work out how to create the following calculation (or if I am doing it the right way).

What I am looking for is that if I enter 250 as the qty it will return the value of 9.25, or if I enter
500 it will return a value of 13.90 or if I enter a value of 2000 it will return a value of 27.55

To get the value of 27.55 it would be 2000 * 0.00915 then adding 9.25 to give the 27.55

Tthe following line entered into the expression builder only works for the 250 and 500 values only.

IIf([Qty]>”250”,”9.25”,IIf([Qty]=”500”,”13.90”,IIf([Qty]>=”1000”,”([Qty]”*”.00915)”+”9.25”)))
am I missing something in the expression.
Go to the top of the page
 
RJD
post Mar 13 2018, 04:17 PM
Post#2


UtterAccess VIP
Posts: 8,461
Joined: 25-October 10
From: Gulf South USA


Hi: If Qty is numeric, then I am surprised the calculation is working at all. When you enclose values in double-quotes, this means they are text (string) values.

Could you tell us if Qty is numeric or text? And could there be other values besides 250, 500, 1000, 2000 ... ? If the Qty values are text, then you should correct that first to numeric. Then we can go from there.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
doctor9
post Mar 13 2018, 04:29 PM
Post#3


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


sk5347,

Could you explain what you need in more detail? Your example expression uses a quantity of 1000 instead of 2000, which you used in your description of the problem, which seems odd.

Also, you're using these conditions in your example expression:
>250
=500
>=1000

These three conditions will ignore a lot of values, specifically 1 through 499 and 501 through 999.

It seems like you have different multipliers for different quantities - maybe it's a sort of bulk discount concept? So, would you maybe have a multiplier of 1 for quantities of 250 or lower? Or zero? Or is this something else?

If you explain the context of what the result represents, that might also help us out. Try explaining what you need in non-database terms, if that would help.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
sk5347
post Mar 14 2018, 03:06 AM
Post#4



Posts: 8
Joined: 23-January 15



Hello Everybody,
And thanks for your feedback.
I’ll try and explain what I am trying to do.

I have an old program written in MS Basic (40 years ago)which only runs under windows 32bit and am trying to rewrite using access.
It is a database for customer quotes.

I am trying to return a numeric value in the qty field which is a text box.
The qty of >250 was a typo it should of had an = sign like the 500 value.
I am am not looking for any values between 1 and 249 the same as 501 to 999.

It is designed if you input 250 into the qty field it would return a value of 9.25,
It would also return a value of 13.90 if 500 was entered
If I enter a value of 1000 it then calculates the value and returns 18.40
It does this by 1000 x 0.00915 then adds the 9.25 to return the value of 18.40
If 5000 was entered it would be 5000 x 0.00915 then add 9.25 to return a value of 55.00

In the qty field I only require the number 250,500 and any number over 1000 entered in the field.
I would also want to add another field to do the same calculation but have the overall qty multiplied by 25%.

Hope I have explained it a bit better, if you need any other info or a copy of what I have done, just ask.
Go to the top of the page
 
projecttoday
post Mar 14 2018, 05:39 AM
Post#5


UtterAccess VIP
Posts: 9,809
Joined: 10-February 04
From: South Charleston, WV


Using a table for lookups is generally the best way.

Can you post the old code?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
sk5347
post Mar 14 2018, 07:38 AM
Post#6



Posts: 8
Joined: 23-January 15



Hello Projecttoday,

Below is one of the lines that refers to the calculation.
Hope this helps.


31020 IF T1#>=0 THEN 31030 ELSE IF PR#=250 THEN PRT#=RC1!(PMC%) ELSE IF PR#=500 THEN PRT#=RC2!(PMC%) ELSE RSET M$="No printing cost - enter [":DFT$="0.00 ":GOSUB 210:PRT#=VAL(IN$)
Go to the top of the page
 
projecttoday
post Mar 14 2018, 09:36 AM
Post#7


UtterAccess VIP
Posts: 9,809
Joined: 10-February 04
From: South Charleston, WV


[code][/code]
QUOTE
If I enter a value of 1000 it then calculates the value and returns 18.40
It does this by 1000 x 0.00915 then adds the 9.25 to return the value of 18.40


I'm a little confused. Why can't you just say if I enter a value of 1000 it returns 18.40?

MS Basic? So this was when Microsoft first started and they made compiler for pc's as opposed to operating systems? I was once a BASIC programmer (Data General).

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
doctor9
post Mar 14 2018, 09:48 AM
Post#8


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


sk5347,

Some context for what this returned value represents in real-world terms would help us to understand what you're trying to accomplish, but here's what I came up with.

> It is designed if you input 250 into the qty field it would return a value of 9.25,
> It would also return a value of 13.90 if 500 was entered
> If I enter a value of 1000 it then calculates the value and returns 18.40
> If 5000 was entered it would be 5000 x 0.00915 then add 9.25 to return a value of 55.00

You could use a Select Case I suppose...

CODE
    Select Case Qty
    Case 250
        ReturnedValue = 9.25
    Case 500
        ReturnedValue = 13.9
    Case Is >= 1000
        ReturnedValue = Qty * 0.00915 + 9.25
    Case Else
        MsgBox "Unknown Qty value"
    End Select


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
RJD
post Mar 14 2018, 10:16 AM
Post#9


UtterAccess VIP
Posts: 8,461
Joined: 25-October 10
From: Gulf South USA


Hi: Pardon me for jumping back in, but it would seem that you could put ...

Switch([Qty]=250,9.25,[Qty]=500,13.9,True,9.25+([Qty]*0.00915)) As Results

...in your query (SQL View) to get the results you want. Assuming you will only have the values you indicated (or larger), and Qty = 0 is never present (and that could be accommodated if necessary).

Also, doing the calculation analysis, I wondered how you came up with the result for 500. It doesn't calculate with the full calculation, yielding 13.825 instead. As I said, just wondering about the logic ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
sk5347
post Mar 14 2018, 02:14 PM
Post#10



Posts: 8
Joined: 23-January 15



Hello All,

Thanks for your input and I will try out all the following suggestions
to see which one I get to work for me with being a beginner.

To answer doctor9 question the quantity represents a money value
for setting up and running (time) a Litho printing machine.

To RJD you are correct I will only have the values 250,500 and larger, also Qty = 0 is never present.

Your calculation analysis on the 500 qty is a good question
I have never questioned the value calculation as it was calculated 40 years ago, and am unable as
my dad has now got Alzheimer's and is the only one who can answer that question.

Thanks once again for helping a beginner
Go to the top of the page
 
projecttoday
post Mar 14 2018, 06:14 PM
Post#11


UtterAccess VIP
Posts: 9,809
Joined: 10-February 04
From: South Charleston, WV


Keith, I sent you a PM. Did you receive it?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th July 2018 - 10:14 PM