MS Access Query how do you create 3 If Statements that use a between function?

[anoble1]

Hi,
I have having some trouble and need some help. I have a program that has Sell Price, Cost, and a Margin Field I just made in the query. I have to make a query that says if you sell at 20% margin to 22.4% margin it will return (1). And If you sell 22.5% margin to 27.4% margin it will return (2) and greater than 27.5% Margin will return (3).

How the heck do I do that? Ideas?

[Seth Schrock]

I would use the Switch() function in your query. It is like the Select Case statement in VBA.

Switch(Margin BETWEEN 0.2 And 0.224, 1
     , Margin BETWEEN 0.225 And 0.274, 2
     , Margin >= 0.275, 3) As MyField

[NeoPa]

It's unfortunate that you haven't specified what you require in those circumstances where none of the above conditions is met. Also, though managers may want to say between 2.0 and 22.4 followed by between 22.5 and 27.4, that doesn't mean it makes sense. Arithmetically you have to realise that following that logic leaves out any result for a 22.45 (for instance).

Thus, accomodating some gueswork and proper logic, we might end up with something like the following :

Switch([Margin]<0.2, 0
     , [Margin]<0.225, 1
     , [Margin]<0.275, 2
     , True, 3) AS [MyField]