Select Case in Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a rather complex series of calculations I have to do on a field in a
query for a silent auction. For instance:
To calc the new field StartBid:
If [Value] is 0 to 50 then multilpy by 0.2
If [Value] is 51 to 100 then multiply by 0.25
If [Value] is greater than 100 then multiply by 0.3
etc.

In addition to this criteria, I would like the result of any thing with
[Value] greater than 50 to be rounded to the nearest 5.

My next calculation is for a new field BidIncrement:
This I need to be:
If [StartBid] is less than 50 then multiply by 0.2 and round to nearest
interger
If [StartBid is 51 to 100 then multiply by 0.25 and round to nearest interger
etc.

In addition, I need any result with a value greater than 50 to be rounded to
the nearest 5.

I'm fairly new to VB, I think I could work this out in a proc with Select
Case, but how do I accomplish this in a Query?

TIA,
Sandra G
 
Sandra Grawunder said:
I have a rather complex series of calculations I have to do on a field in a
query for a silent auction. For instance:
To calc the new field StartBid:
If [Value] is 0 to 50 then multilpy by 0.2
If [Value] is 51 to 100 then multiply by 0.25
If [Value] is greater than 100 then multiply by 0.3
etc.
Click to expand...
I'm fairly new to VB, I think I could work this out in a proc with Select
Case, but how do I accomplish this in a Query?
Click to expand...

Put the Select Case in a function in a module and call it from the query.

Function ConvertValue(n)
Dim x
x = n
Select Case x
Case 0 To 50
x = x * 0.2
Case 51 To 100
x = x * 0.25
Case > 100
x = x * 0.3
End Select

If x > 50 then
x = Int(x / 5 + .5) * 5
End If

ConvertValue = x
End Function


Tom Lake
 
Tom Lake wrote in message said:
Sandra Grawunder said:
I have a rather complex series of calculations I have to do on a field in a
query for a silent auction. For instance:
To calc the new field StartBid:
If [Value] is 0 to 50 then multilpy by 0.2
If [Value] is 51 to 100 then multiply by 0.25
If [Value] is greater than 100 then multiply by 0.3
etc.
Click to expand...
I'm fairly new to VB, I think I could work this out in a proc with Select
Case, but how do I accomplish this in a Query?
Click to expand...

Put the Select Case in a function in a module and call it from the query.

Function ConvertValue(n)
Dim x
x = n
Select Case x
Case 0 To 50
x = x * 0.2
Case 51 To 100
x = x * 0.25
Case > 100
x = x * 0.3
End Select

If x > 50 then
x = Int(x / 5 + .5) * 5
End If

ConvertValue = x
End Function


Tom Lake
Click to expand...

Perhaps the Switch function within the query?

select myfield *
switch(myfield<=50, 0.2, myfield<=100, 0.25, myfield>100, 0.3)
 
Hi Sandra,

Like Tom I feel it would be simpler to do this by calling custom VBA
functions from the query. But if you want to explore an all-SQL
solution, check out the SWITCH() function, e.g. this (which may not give
the results you want but shows the sort of thing that can be done.

SWITCH([Value]<=50, 0.2, [Value]<=100, ROUND([Value] * 0.25/5, 0)*5,
TRUE, ROUND([Value] * 0.3/5, 0)*5)

SWITCH evaluates each expression, looking at the first expression in
each pair (from left to right) to see if it evaluates to TRUE, and if it
does returning the value of the second expression in the pair. So
putting TRUE as the first expression in the last pair gives a default
case, like Case Else in a VBA Select Case structure.



I have a rather complex series of calculations I have to do on a field in a
query for a silent auction. For instance:
To calc the new field StartBid:
If [Value] is 0 to 50 then multilpy by 0.2
If [Value] is 51 to 100 then multiply by 0.25
If [Value] is greater than 100 then multiply by 0.3
etc.

In addition to this criteria, I would like the result of any thing with
[Value] greater than 50 to be rounded to the nearest 5.

My next calculation is for a new field BidIncrement:
This I need to be:
If [StartBid] is less than 50 then multiply by 0.2 and round to nearest
interger
If [StartBid is 51 to 100 then multiply by 0.25 and round to nearest interger
etc.

In addition, I need any result with a value greater than 50 to be rounded to
the nearest 5.

I'm fairly new to VB, I think I could work this out in a proc with Select
Case, but how do I accomplish this in a Query?

TIA,
Sandra G
Click to expand...
 
OK, I would feel more comfortable with a function because if have written
case statements before. So my next dumb question is: How do I call the
function in the query? I would put in the field box StartBid: SomeFunction ?

And last dumb question - Where do I rate the messages?

Thanks! SLG

Tom Lake said:
Sandra Grawunder said:
I have a rather complex series of calculations I have to do on a field in a
query for a silent auction. For instance:
To calc the new field StartBid:
If [Value] is 0 to 50 then multilpy by 0.2
If [Value] is 51 to 100 then multiply by 0.25
If [Value] is greater than 100 then multiply by 0.3
etc.
Click to expand...
I'm fairly new to VB, I think I could work this out in a proc with Select
Case, but how do I accomplish this in a Query?
Click to expand...

Put the Select Case in a function in a module and call it from the query.

Function ConvertValue(n)
Dim x
x = n
Select Case x
Case 0 To 50
x = x * 0.2
Case 51 To 100
x = x * 0.25
Case > 100
x = x * 0.3
End Select

If x > 50 then
x = Int(x / 5 + .5) * 5
End If

ConvertValue = x
End Function


Tom Lake
Click to expand...
 
Back
Top