Switch Function TOO Complex

[Asib]

Good afternoon all, i am in need od assistance with a switch function i was
trying to use in one of my queries.

Switch([1stTotPrice] >= 0 And [1stTotPrice] <= 20, 0, [1stTotPrice] >= 20.01
And [1stTotPrice] <= 40, 7, [1stTotPrice] >= 40.01 And [1stTotPrice] <= 100,
19.45, [1stTotPrice] >= 100.01 And [1stTotPrice] <= 200, 29, [1stTotPrice] >=
200.01 And [1stTotPrice] <= 350, 42.95, [1stTotPrice] >= 350.01 And
[1stTotPrice] <= 500, 48.25, [1stTotPrice] >= 500.01 And [1stTotPrice] <=
750, 54.85, [1stTotPrice] >= 750.01 And [1stTotPrice] < 1000, 61.4,
[1stTotPrice] > 1000.01 And [1stTotPrice] < 1250, 67.95, [1stTotPrice] >
1250.01 And [1stTotPrice] < 1600, 72.25, [1stTotPrice] >= 1600.01 And
[1stTotPrice] <= 5000, 76.2, [1stTotPrice] >= 5000.01 And [1stTotPrice] <=
6000, 76.2 + 5.9, [1stTotPrice] >= 6000.01 And [1stTotPrice] <= 7000, 76.2 +
11.8, [1stTotPrice] >= 7000.01 And [1stTotPrice] <= 8000, 76.2 + 17.7)

It became to complex to be handled. What i need help with is addapting it to
a public function. I have used them in the past but i am still learning so i
could not get the syntax right. I know it involves If then or if then else,
but i couldnt get it right.
Any and all assistance is greatly appreciated.

Thanks, Aasibs

 

[CJ Ardash]

Have you considered using the Select Case construction? It works through the
list of cases you enter, executes whatever commands you have under the first
true condition and answers. Here's an example:


Select Case bytWeekday
Case 1
msgbox "Yea! It's Sunday!"
Case 7
MsgBox "Yea! It's Saturday"
Case Else
MsgBox "It's a weekday."
End Select

Here's how I'd set up your Switch function:

Select Case 1stTotPrice
Case 0 to 20
sngResult = 0
Case 20 to 40
sngResult = 7
Case 40 to 100
sngResult = 19.45
Case 100 to 200
sngResult = 29,
Case 200 to 350
sngResult = 42.95
Case 350 to 500
sngResult = 48.25
Case 500 to 750,
sngResult = 54.85
Case 750 to 1000
sngResult = 61.4
Case 1000 to 1250
sngResult = 67.95
Case 1250to 1600
sngResult = 72.25
Case 1600 to 5000
sngResult = 76.2
Case 5000.to 6000
sngResult = 76.2 + 5.9
Case 6000. to 7000
sngResult = 76.2 + 11.8
Case 7000 to 8000
sngResult = 76.2 + 17.7
End Select

Hope this helps,
CJ

 

[Dirk Goldgar]

Good afternoon all, i am in need od assistance with a switch function i
was
trying to use in one of my queries.

Switch([1stTotPrice] >= 0 And [1stTotPrice] <= 20, 0, [1stTotPrice] >=
20.01
And [1stTotPrice] <= 40, 7, [1stTotPrice] >= 40.01 And [1stTotPrice] <=
100,
19.45, [1stTotPrice] >= 100.01 And [1stTotPrice] <= 200, 29, [1stTotPrice]

=

200.01 And [1stTotPrice] <= 350, 42.95, [1stTotPrice] >= 350.01 And
[1stTotPrice] <= 500, 48.25, [1stTotPrice] >= 500.01 And [1stTotPrice] <=
750, 54.85, [1stTotPrice] >= 750.01 And [1stTotPrice] < 1000, 61.4,
[1stTotPrice] > 1000.01 And [1stTotPrice] < 1250, 67.95, [1stTotPrice] >
1250.01 And [1stTotPrice] < 1600, 72.25, [1stTotPrice] >= 1600.01 And
[1stTotPrice] <= 5000, 76.2, [1stTotPrice] >= 5000.01 And [1stTotPrice] <=
6000, 76.2 + 5.9, [1stTotPrice] >= 6000.01 And [1stTotPrice] <= 7000, 76.2
+
11.8, [1stTotPrice] >= 7000.01 And [1stTotPrice] <= 8000, 76.2 + 17.7)

It became to complex to be handled. What i need help with is addapting it
to
a public function. I have used them in the past but i am still learning so
i
could not get the syntax right. I know it involves If then or if then
else,
but i couldnt get it right.

Although you *can* do it with a series of If/Then/Else statements, this is a
classic situation for a Select Case statement. Try something like this for
your function:

'------ start of code ------
Function fncPriceResult(PriceIn As Variant) As Variant

Select Case PriceIn
Case Is Between 0 And 20: fncPriceResult = 0
Case Is <= 40: fncPriceResult = 7
Case Is <= 100: fncPriceResult = 19.45
Case Is <= 200: fncPriceResult = 29
Case Is <= 350: fncPriceResult = 42.95
Case Is <= 500: fncPriceResult = 48.25
Case Is <= 750: fncPriceResult = 54.85
Case Is <= 1000: fncPriceResult = 61.4
Case Is <= 1250: fncPriceResult = 67.95
Case Is <= 1600: fncPriceResult = 72.25
Case Is <= 5000: fncPriceResult = 76.2
Case Is <= 6000: fncPriceResult = 76.2 + 5.9
Case Is <= 7000: fncPriceResult = 76.2 + 11.8
Case Is <= 8000: fncPriceResult = 76.2 + 17.7
Case Else: fncPriceResult = Null
End Select

End Function
'------ end of code ------

Some things to note:

1. In a couple of the cases in your original Switch expression, you had ">"
and "<" instead of ">=" and "<=", but I assumed those were errors. If I was
wrong, change the cases in the Select block accordingly.

2. Your Switch expression didn't say what should happen if [1stTotPrice]
didn't fall into any of the cases. I have the Select statement set to
return Null.

3. I don't see any special reason to return expressions like "76.2 + 5.9",
instead of the actual sum, "82.1". But I left that as coded in case it
makes it clearer to you.

4. It isn't clear what data type you want returned. If it's currency, the
numeric literals could be specified to be currency data type; e.g.,

fncPriceResult = 7@

The type-declaration character "@" identifies the constant as a currency
value. Precise typing would be to your advantage.

 

[Dirk Goldgar]

Case Is Between 0 And 20: fncPriceResult = 0

CORRECTION: For some reason I thought that was the right syntax, but it
isn't. That should be:

Case 0 To 20: fncPriceResult = 0

 

[vanderghast]

I doubt that these numbers will be eternal. Someone, one day, may well
decide to modify some constant or add some ranges. Better to use a table:

UpToPrice Returned ' fields
20 0
40 7
100 19.45
.... ' data


Now that the data is in a table, it can be modified WITHOUT having to TOUCH
THE CODE.


And the expression? Simply:


DMIN( "Returned", "TableNameHere", [lstToPrice] & " <= UpToPrice")


should do.



(I assumed the Returned value increases as UpToPrice supplied value also
increases, so the simple DMin is appropriate).




Vanderghast, Access MVP

Good afternoon all, i am in need od assistance with a switch function i
was
trying to use in one of my queries.

Switch([1stTotPrice] >= 0 And [1stTotPrice] <= 20, 0, [1stTotPrice] >=
20.01
And [1stTotPrice] <= 40, 7, [1stTotPrice] >= 40.01 And [1stTotPrice] <=
100,
19.45, [1stTotPrice] >= 100.01 And [1stTotPrice] <= 200, 29, [1stTotPrice]

=

200.01 And [1stTotPrice] <= 350, 42.95, [1stTotPrice] >= 350.01 And
[1stTotPrice] <= 500, 48.25, [1stTotPrice] >= 500.01 And [1stTotPrice] <=
750, 54.85, [1stTotPrice] >= 750.01 And [1stTotPrice] < 1000, 61.4,
[1stTotPrice] > 1000.01 And [1stTotPrice] < 1250, 67.95, [1stTotPrice] >
1250.01 And [1stTotPrice] < 1600, 72.25, [1stTotPrice] >= 1600.01 And
[1stTotPrice] <= 5000, 76.2, [1stTotPrice] >= 5000.01 And [1stTotPrice] <=
6000, 76.2 + 5.9, [1stTotPrice] >= 6000.01 And [1stTotPrice] <= 7000, 76.2
+
11.8, [1stTotPrice] >= 7000.01 And [1stTotPrice] <= 8000, 76.2 + 17.7)

It became to complex to be handled. What i need help with is addapting it
to
a public function. I have used them in the past but i am still learning so
i
could not get the syntax right. I know it involves If then or if then
else,
but i couldnt get it right.
Any and all assistance is greatly appreciated.

Thanks, Aasibs

 

[Asib]

Thank you all for the great responses. This is such a fantastic
community. They all work great but i decided to go with the table design for
the ease of future maintenance. I had never used the dmin function before. I
am still learning as i go. Again thank you all for the help.

Aasibs

I doubt that these numbers will be eternal. Someone, one day, may well
decide to modify some constant or add some ranges. Better to use a table:

UpToPrice Returned ' fields
20 0
40 7
100 19.45
... ' data


Now that the data is in a table, it can be modified WITHOUT having to TOUCH
THE CODE.


And the expression? Simply:


DMIN( "Returned", "TableNameHere", [lstToPrice] & " <= UpToPrice")


should do.



(I assumed the Returned value increases as UpToPrice supplied value also
increases, so the simple DMin is appropriate).




Vanderghast, Access MVP

Good afternoon all, i am in need od assistance with a switch function i
was
trying to use in one of my queries.

Switch([1stTotPrice] >= 0 And [1stTotPrice] <= 20, 0, [1stTotPrice] >=
20.01
And [1stTotPrice] <= 40, 7, [1stTotPrice] >= 40.01 And [1stTotPrice] <=
100,
19.45, [1stTotPrice] >= 100.01 And [1stTotPrice] <= 200, 29, [1stTotPrice]

=

200.01 And [1stTotPrice] <= 350, 42.95, [1stTotPrice] >= 350.01 And
[1stTotPrice] <= 500, 48.25, [1stTotPrice] >= 500.01 And [1stTotPrice] <=
750, 54.85, [1stTotPrice] >= 750.01 And [1stTotPrice] < 1000, 61.4,
[1stTotPrice] > 1000.01 And [1stTotPrice] < 1250, 67.95, [1stTotPrice] >
1250.01 And [1stTotPrice] < 1600, 72.25, [1stTotPrice] >= 1600.01 And
[1stTotPrice] <= 5000, 76.2, [1stTotPrice] >= 5000.01 And [1stTotPrice] <=
6000, 76.2 + 5.9, [1stTotPrice] >= 6000.01 And [1stTotPrice] <= 7000, 76.2
+
11.8, [1stTotPrice] >= 7000.01 And [1stTotPrice] <= 8000, 76.2 + 17.7)

It became to complex to be handled. What i need help with is addapting it
to
a public function. I have used them in the past but i am still learning so
i
could not get the syntax right. I know it involves If then or if then
else,
but i couldnt get it right.
Any and all assistance is greatly appreciated.

Thanks, Aasibs