Switch Function

[Guest]

I am trying to use the switch function in a query.
I am trying to get the result to be as follows
when [down]=2 And [distance] >3 And <7

I am not sure exactly how to write that?

Here is what I have:
ddgroup: Switch([down]=2 And [distance]>3 And <7,"2 & Medium")

 

[David F Cox]

Switch([down]=2 And [distance] BETWEEN 4 AND 6,"2 & Medium")

might be what you require if integer values are being tested for. . If
there is just one condition you could use IIF

IIF(([down]=2 AND [distance] BETWEEN 4 AND 6,"2 & Medium")

 

[Brendan Reynolds]

You don't need the Switch function unless you have a list of expressions to
evaluate. With just one expression to evaluate, you can use the IIF
function. Either way, though, you've only defined what the result should be
when the condition is true. You also need to define what the result should
be when the condition is false. Finally, you have a syntax error. You need
to include the field name twice: '[distance] > 3 And [distance] < 7'. A
complete IIf call might look something like ...

ddgroup: IIf([down] = 2 And ([distance] > 3 And [distance] < 7), "2 &
Medium", "X")

.... where 'X' is whatever you want the result to be if the condition is
false.

BTW: Is 'Medium' a literal value or a field name? If it is a field name, you
may perhaps have your quotes in the wrong place, you may want ...

"2 " & [Medium]

.... rather than "2 & Medium"

 

[Marshall Barton]

I am trying to use the switch function in a query.
I am trying to get the result to be as follows
when [down]=2 And [distance] >3 And <7

I am not sure exactly how to write that?

Here is what I have:
ddgroup: Switch([down]=2 And [distance]>3 And <7,"2 & Medium")

If you only have one expression to check, you should use IIf
instead of Switch:

ddgroup: IIf([down]=2 And ([distance] Between 4 And 6),"2 &
Medium", "something when condition not met")

If you really need to use Switch, please provide the rest of
the puzzle.

 

[Guest]

There are more than 1 different possibilities. In fact there are many and I
only included the one. I appreciate your help with that. That part of it now
works. Now my problem is that the statement is too long and it won't let me
write everything that I need in there without returning the statement that it
is too complex? Any suggestions for how I handle this?

I am trying to use the switch function in a query.
I am trying to get the result to be as follows
when [down]=2 And [distance] >3 And <7

I am not sure exactly how to write that?

Here is what I have:
ddgroup: Switch([down]=2 And [distance]>3 And <7,"2 & Medium")

If you only have one expression to check, you should use IIf
instead of Switch:

ddgroup: IIf([down]=2 And ([distance] Between 4 And 6),"2 &
Medium", "something when condition not met")

If you really need to use Switch, please provide the rest of
the puzzle.

 

[John Vinson]

There are more than 1 different possibilities. In fact there are many and I
only included the one. I appreciate your help with that. That part of it now
works. Now my problem is that the statement is too long and it won't let me
write everything that I need in there without returning the statement that it
is too complex? Any suggestions for how I handle this?

Without knowing what it is that you want to "handle", it's very hard
to say. You might need a more complex Switch; you might need to write
custom VBA code; but you might also be able to develop a query
solution, perhaps by creating a "range" table with fields like Down,
DistanceLow, DistanceHigh, and Description. (I take it this is
American football...?)

You could have one row for each situation:

Down DistanceLow DistanceHigh Description
2 3 7 "2 & Medium"
2 7 12 "2 & Long"
2 0 3 "2 & Short"
2 12 25 "2 & Deep"
4 25 100 "Desperate Quarterback"

You can create a Query joining this to your other table by a "Non Equi
Join"

.... INNER JOIN tblTranslate
ON yourtable.Down = tblTranslate.Down
AND yourtable.Distance >= tblTranslate.DistanceLow
AND yourtable.Distance < tblTranslate.DistanceHigh

This has the advantage that you can change the descriptions or the
cutoff distances in a table, rather than digging through a long
complex expression or VBA code.

John W. Vinson[MVP]

 

[Guest]

John,
Yes it is American football. Just trying to figure out a way to win a game
here and create a database.
Thank you so much. You are now talking about exactly what I was hoping to do
earlier without knowing how to do it.

I now have a table called 'main' in that table is all my data including
[down] [distance] etc...

I created a table called 'dandd' in that table I included 4 fields.
[danddid] [down] [distancelow] [distancehigh]

Can you show me a sample of what the sql should look like following your
example bellow? I greatly appreciate your help.

I tried the following and I am receiving the message "type mismatch in
expression"

SELECT main.down, main.distance, dandd.name
FROM main INNER JOIN dandd

 

[Guest]

John,

Thanks I figured it out. I just had to match the data types in both tables.
Sorry for the extra question. I really appreciate your help. I am back up and
rolling.

 

[John Vinson]

I tried the following and I am receiving the message "type mismatch in
expression"

SELECT main.down, main.distance, dandd.name
FROM main INNER JOIN dandd
ON main.down = dandd.down AND main.distance > dandd.distancelow AND
main.distance < dandd.distancehigh

That suggests that you have a type mismatch in the expression! What
are the datatypes of the fields down, distance, distancelow and
distancehigh? They need to match in the two tables.

John W. Vinson[MVP]