Selecting data with CHOOSE

  • Thread starter Thread starter Tor Inge Rislaa
  • Start date Start date
T

Tor Inge Rislaa

John Viescas was so kind to reply with this solution to my question
yesterday:

SELECT MyValue, Choose([MyValue] - 1, "External", "Internal") As
ValueDescription
FROM MyTable

I can't find any reference to the command CHOOSE. I would appreciate if
someone could give me a description on the Syntax for the command CHOOSE so
that I could understand how it works. Is it for instance possible to
evaluate more than two values and so on.?




REF my question yesterday:

I have an simple query as below:
SELECT MyValue
FROM MyTable

Based on the value in the field MyValue I want to add a value to a new
column in the result of the query.

Example:
If the value in MyValue = 0 then I would add the text "External"
If the value in MyValue = 1 then I would add the text "Internal"
I what to see the same result as the query below (on the fly), without using
union all:

SELECT MyValue, "External" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
UNION ALL
SELECT MyValue, "Internal" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
 
If you're using Access 2000 or 2002, the best way to find help topics like
this is to press Ctrl-G to open the Immediate window in Visual Basic, type
the keyword, highlight it, and press F1. The function topics aren't
properly linked to the main Access interface in these releases, so you have
to go to VB to find them. Here's the topic:

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value
between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.



Remarks

Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For
example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and
choice-3 = "three", Choose returns "three". This capability is particularly
useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one.
For this reason, you should watch for undesirable side effects. For example,
if you use the MsgBox function as part of an expression in all the choices,
a message box will be displayed for each choice as it is evaluated, even
though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than
the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number
before being evaluated.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Tor Inge Rislaa said:
John Viescas was so kind to reply with this solution to my question
yesterday:

SELECT MyValue, Choose([MyValue] - 1, "External", "Internal") As
ValueDescription
FROM MyTable

I can't find any reference to the command CHOOSE. I would appreciate if
someone could give me a description on the Syntax for the command CHOOSE so
that I could understand how it works. Is it for instance possible to
evaluate more than two values and so on.?




REF my question yesterday:

I have an simple query as below:
SELECT MyValue
FROM MyTable

Based on the value in the field MyValue I want to add a value to a new
column in the result of the query.

Example:
If the value in MyValue = 0 then I would add the text "External"
If the value in MyValue = 1 then I would add the text "Internal"
I what to see the same result as the query below (on the fly), without using
union all:

SELECT MyValue, "External" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
UNION ALL
SELECT MyValue, "Internal" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
Click to expand...
 
Is there a way to evaluate string expression the same way as numeric values.

If MyValue Like "A" then the description should be "First Value"

If MyValue Like "B" then the description should be "Second Value"

TIRislaa

John Viescas said:
If you're using Access 2000 or 2002, the best way to find help topics like
this is to press Ctrl-G to open the Immediate window in Visual Basic, type
the keyword, highlight it, and press F1. The function topics aren't
properly linked to the main Access interface in these releases, so you have
to go to VB to find them. Here's the topic:

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value
between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.



Remarks

Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For
example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and
choice-3 = "three", Choose returns "three". This capability is particularly
useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one.
For this reason, you should watch for undesirable side effects. For example,
if you use the MsgBox function as part of an expression in all the choices,
a message box will be displayed for each choice as it is evaluated, even
though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than
the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number
before being evaluated.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Tor Inge Rislaa said:
John Viescas was so kind to reply with this solution to my question
yesterday:

SELECT MyValue, Choose([MyValue] - 1, "External", "Internal") As
ValueDescription
FROM MyTable

I can't find any reference to the command CHOOSE. I would appreciate if
someone could give me a description on the Syntax for the command CHOOSE so
that I could understand how it works. Is it for instance possible to
evaluate more than two values and so on.?




REF my question yesterday:

I have an simple query as below:
SELECT MyValue
FROM MyTable

Based on the value in the field MyValue I want to add a value to a new
column in the result of the query.

Example:
If the value in MyValue = 0 then I would add the text "External"
If the value in MyValue = 1 then I would add the text "Internal"
I what to see the same result as the query below (on the fly), without using
union all:

SELECT MyValue, "External" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
UNION ALL
SELECT MyValue, "Internal" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
Click to expand...
Click to expand...
 
If MyValue is a single character then you could use:
Choose(Instr("AB",[MyValue]),"First Value", "Second Value")

--
Duane Hookom
MS Access MVP


Tor Inge Rislaa said:
Is there a way to evaluate string expression the same way as numeric values.

If MyValue Like "A" then the description should be "First Value"

If MyValue Like "B" then the description should be "Second Value"

TIRislaa

John Viescas said:
If you're using Access 2000 or 2002, the best way to find help topics like
this is to press Ctrl-G to open the Immediate window in Visual Basic, type
the keyword, highlight it, and press F1. The function topics aren't
properly linked to the main Access interface in these releases, so you have
to go to VB to find them. Here's the topic:

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value
between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.



Remarks

Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For
example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and
choice-3 = "three", Choose returns "three". This capability is particularly
useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one.
For this reason, you should watch for undesirable side effects. For example,
if you use the MsgBox function as part of an expression in all the choices,
a message box will be displayed for each choice as it is evaluated, even
though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than
the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number
before being evaluated.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Tor Inge Rislaa said:
John Viescas was so kind to reply with this solution to my question
yesterday:

SELECT MyValue, Choose([MyValue] - 1, "External", "Internal") As
ValueDescription
FROM MyTable

I can't find any reference to the command CHOOSE. I would appreciate if
someone could give me a description on the Syntax for the command
Click to expand...
CHOOSE
so
that I could understand how it works. Is it for instance possible to
evaluate more than two values and so on.?




REF my question yesterday:

I have an simple query as below:
SELECT MyValue
FROM MyTable

Based on the value in the field MyValue I want to add a value to a new
column in the result of the query.

Example:
If the value in MyValue = 0 then I would add the text "External"
If the value in MyValue = 1 then I would add the text "Internal"
I what to see the same result as the query below (on the fly), without using
union all:

SELECT MyValue, "External" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
UNION ALL
SELECT MyValue, "Internal" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
Click to expand...
Click to expand...
Click to expand...
 
See Duane's answer. You can also use Switch:

Switch([MyValue]="A", "First Value", [MyValue]="B", "Second Value")

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Tor Inge Rislaa said:
Is there a way to evaluate string expression the same way as numeric values.

If MyValue Like "A" then the description should be "First Value"

If MyValue Like "B" then the description should be "Second Value"

TIRislaa

John Viescas said:
If you're using Access 2000 or 2002, the best way to find help topics like
this is to press Ctrl-G to open the Immediate window in Visual Basic, type
the keyword, highlight it, and press F1. The function topics aren't
properly linked to the main Access interface in these releases, so you have
to go to VB to find them. Here's the topic:

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value
between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.



Remarks

Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For
example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and
choice-3 = "three", Choose returns "three". This capability is particularly
useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one.
For this reason, you should watch for undesirable side effects. For example,
if you use the MsgBox function as part of an expression in all the choices,
a message box will be displayed for each choice as it is evaluated, even
though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than
the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number
before being evaluated.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Tor Inge Rislaa said:
John Viescas was so kind to reply with this solution to my question
yesterday:

SELECT MyValue, Choose([MyValue] - 1, "External", "Internal") As
ValueDescription
FROM MyTable

I can't find any reference to the command CHOOSE. I would appreciate if
someone could give me a description on the Syntax for the command
Click to expand...
CHOOSE
so
that I could understand how it works. Is it for instance possible to
evaluate more than two values and so on.?




REF my question yesterday:

I have an simple query as below:
SELECT MyValue
FROM MyTable

Based on the value in the field MyValue I want to add a value to a new
column in the result of the query.

Example:
If the value in MyValue = 0 then I would add the text "External"
If the value in MyValue = 1 then I would add the text "Internal"
I what to see the same result as the query below (on the fly), without using
union all:

SELECT MyValue, "External" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
UNION ALL
SELECT MyValue, "Internal" AS ValueDescription
FROM MyTable
WHERE MyValue = 0
Click to expand...
Click to expand...
Click to expand...
 
Back
Top