Function with Case (1 Viewer)

[Desus]

First this problem started when I wanted to use Switch Function in a Query field like this one

Amount: Switch([Price]<=10,10,[Price]<=20,20,[Price]<=50,50,[Price]<=100,100,[Price]<=200,200,[Price]<=300,300, [Price]<=400,400,[Price]<=500,500,[Price]<=600,600,[Price]<=700,700,[Price]<=800,800,[Price]<=1000,1000,[Price]<=2000,2000,[Price]<=3000,3000, [Price]<=5000,5000,[Price]<=10000,10000,[Price]<20000,20000,[Price]>=20000,[Price])

But it gave me a Warning "Switch function is too complex" something like this

So I Tried the VBA Function() Using Case Code like this

Function CoNum(Num As Double) As Double

Select Case Num
Case 0 To 10
CoNum = 10
Case 11 To 20
CoNum = 20
Case 21 To 50
CoNum = 50
Case 51 To 100
CoNum = 100
Case 101 To 200
CoNum = 200
Case 201 To 300
CoNum = 300
Case 301 To 400
CoNum = 400
Case 401 To 500
CoNum = 500
Case 501 To 600
CoNum = 600
Case 601 To 700
CoNum = 700
Case 701 To 800
CoNum = 800
Case 801 To 1000
CoNum = 1000
Case 1001 To 2000
CoNum = 2000
Case 2001 To 3000
CoNum = 3000
Case 3001 To 5000
CoNum = 5000
Case 5001 To 10000
CoNum = 10000
Case 10001 To 19999
CoNum = 20000

End Select

this sure will return the value that I want but, What I need now is how to use the Case else if the Num is >20000 and return true [Price] field Value

Please Enlighten me as I'm just a Newbie with programming .. Thank you..

Nevermind, I've found the way out, didn't think about using a function in a function

In the Query Field

Amount: IIf([Price]>20000,[Price],CoNum([Price]))

 

[Grumm]

Or you can just add a Case Else before your End Select...
See more info : https://msdn.microsoft.com/en-us/library/cy37t14y.aspx

 

[namliam]

Or simply store this information in a table instead.... then simply join the two tables to get the needed information.... added bonuses all round
- easier to maintain for an average user
- easier to implement version control (this for 2014 that for 2015)
- more generic

 

[Desus]

Or simply store this information in a table instead.... then simply join the two tables to get the needed information.... added bonuses all round
- easier to maintain for an average user
- easier to implement version control (this for 2014 that for 2015)
- more generic

I've tried this step, it's also didn't meet my expectation ..
or maybe I did the wrong step ?

I made a table name "test"
CoNum Num
10 10
20 20
50 50
100 100
--- ---
20000 20000

and then, in the form, I made a relationship between "test" and "goods"(consist [Price]) table
and in the form's field I have this
CoNum(test),Total Price(sum of [Price] in table "goods")

So I need CoNum to display all [Price] from "goods" <= 10 to display 10 and Total Price to sum all of the [Price] <= 10.

I don't know what expression / function that I need to use to make this done with "Table Relationship"
My Problem is now solved, But if you can help me with this in another way, I can learn new thing from this Relationship and function rules ..

Thank you very much ..

 

[Galaxiom]

Select Case will return on the first match so a series of Less Than can be used instead of the Ranges. This can be important when accepting a Double or Single parameter because, depending on the calculations, the small errors when handing fractional real numbers can cause some values to fall between the ranges.

In the example above a value of 600.001 would be lost. because it is neither between 501 and 600 nor between 601 and 700.

BTW You might consider using the Partition() function instead. This one is not well known because many sites dealing with Access have overlooked it.

 

[Desus]

Select Case will return on the first match so a series of Less Than can be used instead of the Ranges. This can be important when accepting a Double or Single parameter because, depending on the calculations, the small errors when handing fractional real numbers can cause some values to fall between the ranges.

In the example above a value of 600.001 would be lost. because it is neither between 501 and 600 nor between 601 and 700.

BTW You might consider using the Partition() function instead. This one is not well known because many sites dealing with Access have overlooked it.

That's something new to learn about using Single and Double Number Formats,
now that Partition() function is really new to me, and never did I see a function like this has been used by people on the Internet.
Can you give me an Example for how to use it ?

Thank you very much ..

 

[arnelgp]

is it possible to use vba.partition on this, when there is a variable intervals, ie, by 10, 100, 1000.

 

[Grumm]

Select Case will return on the first match so a series of Less Than can be used instead of the Ranges. This can be important when accepting a Double or Single parameter because, depending on the calculations, the small errors when handing fractional real numbers can cause some values to fall between the ranges.

In the example above a value of 600.001 would be lost. because it is neither between 501 and 600 nor between 601 and 700.

BTW You might consider using the Partition() function instead. This one is not well known because many sites dealing with Access have overlooked it.

That is mostly why you should add a "case else" to handle cases that don't exists or are between two values.

 

[Brianwarnock]

That is mostly why you should add a "case else" to handle cases that don't exists or are between two values.

I agree, I always used Case Else as an error message case, not to mop up those I could not be bothered coding directly.

Brian

 

[Galaxiom]

now that Partition() function is really new to me, and never did I see a function like this has been used by people on the Internet.
Can you give me an Example for how to use it ?

https://support.office.com/en-us/article/Partition-Function-1a846a33-60c7-4371-8e77-c94278274dc5

However as arnelgp has pointed out, the interval is not consistent so Partition would not be suitable. I didn't look close enough to notice.

is it possible to use vba.partition on this, when there is a variable intervals, ie, by 10, 100, 1000.

 

[Galaxiom]

That's something new to learn about using Single and Double Number Formats,

A great example of what can happen. Paste this into the Immediate Window and hit return:

? Int(CDbl(90) * CDbl(0.7))

You would expect the result to be 63 but it isn't because of the inaccuracy in the handling of decimal fractions as binary.

This expression gets the expected answer because it uses Decimal datatype:

? Int(CDec(90) * CDec(0.7))

Decimal stores the numbers as integers with a scaling factor.

 

[Desus]

That is mostly why you should add a "case else" to handle cases that don't exists or are between two values.

I agree, I always used Case Else as an error message case, not to mop up those I could not be bothered coding directly.

Brian

Yes, I can agree with you, but what I need is that after "Case Else", it'll return [Price] True Value ( Original Value ) let's say that the [Price] is 26500, so I want it to return 26500.

I don't know what should I do after I put in "Case Else" so I left it be.

However as arnelgp has pointed out, the interval is not consistent so Partition would not be suitable. I didn't look close enough to notice.

I've searched some information about partition too .. and it's really not suitable in my case ..

A great example of what can happen. Paste this into the Immediate Window and hit return:

? Int(CDbl(90) * CDbl(0.7))

You would expect the result to be 63 but it isn't because of the inaccuracy in the handling of decimal fractions as binary.

This expression gets the expected answer because it uses Decimal datatype:

? Int(CDec(90) * CDec(0.7))

Decimal stores the numbers as integers with a scaling factor.

I've tried this too .. and this really surprised me, I didn't think that single and double can be this much different .. gonna change my code into <=
Thanks ..

 

[namliam]

I made a table name "test"
CoNum Num
10 10
20 20
50 50
100 100
--- ---
20000 20000

Start    End      Price
0        10       5.99
11       20       5.95
21       50       5.90
51       100      5.80
10001    20000    5.70
20001    99999    5.50

While you cant make a relationship perse between your tables you would use a join like
Value >= Start
and value <= End