By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

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

P: 223
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?
Jan 18 '18 #1
Share this Question
Share on Google+
2 Replies

Seth Schrock
Expert 2.5K+
P: 2,937
I would use the Switch() function in your query. It is like the Select Case statement in VBA.
Expand|Select|Wrap|Line Numbers
  1. Switch(Margin BETWEEN 0.2 And 0.224, 1
  2.      , Margin BETWEEN 0.225 And 0.274, 2
  3.      , Margin >= 0.275, 3) As MyField
Jan 18 '18 #2

Expert Mod 15k+
P: 31,419
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 :
Expand|Select|Wrap|Line Numbers
  1. Switch([Margin]<0.2, 0
  2.      , [Margin]<0.225, 1
  3.      , [Margin]<0.275, 2
  4.      , True, 3) AS [MyField]
Jan 18 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.