Limits to the Switch Fonction

  • Thread starter Thread starter Telesphore
  • Start date Start date
T

Telesphore

In my case, the Switch fonction in the source control of a textbox accepted
only 15 arguments. At the 16th, Access says "Too complex expression."
Suggestions for a workaround?
Thank you in advance.
 
Here it is:

=Switch(
[StudentType]="CB";"Campus Belgium";
[StudentType]="CF";"Campus Farel";
[StudentType]="CT";"Campus Tahiti";
..
)

It seems I'll need to use a Function like Select Case, but how would be the
code.

Thanks again
 
That cries out for a translation table - Two Columns
StudentType and CampusName
One record for each abbreviation and campus name

Then you join the the translation table to your table in the query on the
StudentType fields and get the associated campus name.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Here it is:

=Switch(
[StudentType]="CB";"Campus Belgium";
[StudentType]="CF";"Campus Farel";
[StudentType]="CT";"Campus Tahiti";
.
)

It seems I'll need to use a Function like Select Case, but how would be
the code.

Thanks again



Jeff Boyce said:
If you'll offer more specific description of what you're using Switch
to accomplish, folks here may be able to offer more specific
suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
I am still "fighting" with Select Case!
The following code would work if there were no pase between "Type" and
"Étudiant".
Access says "Compilation Error" "Waiting for: Identificator"

Option Compare Database
Function Equivalence([Type Étudiant] As String) As String
Select Case [Type Étudiant]
Case "CB"
Equivalence = "Belgique"
Case "CF"
Equivalence = "Farel"
Case "CT"
Equivalence = "Tahiti"
End Select
End Function




John Spencer said:
That cries out for a translation table - Two Columns
StudentType and CampusName
One record for each abbreviation and campus name

Then you join the the translation table to your table in the query on the
StudentType fields and get the associated campus name.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Here it is:

=Switch(
[StudentType]="CB";"Campus Belgium";
[StudentType]="CF";"Campus Farel";
[StudentType]="CT";"Campus Tahiti";
.
)

It seems I'll need to use a Function like Select Case, but how would be
the code.

Thanks again



Jeff Boyce said:
If you'll offer more specific description of what you're using Switch to
accomplish, folks here may be able to offer more specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

In my case, the Switch fonction in the source control of a textbox
accepted only 15 arguments. At the 16th, Access says "Too complex
expression."
Suggestions for a workaround?
Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Arguments to a function are variables. Variable names cannot contain spaces.


Your function might look like the following.
Option Compare Database
Function Equivalence(StudentType As String) As String
Select Case StudentType
Case "CB"
Equivalence = "Belgique"
Case "CF"
Equivalence = "Farel"
Case "CT"
Equivalence = "Tahiti"
End Select
End Function

To call that function, you would use the expression
Equivalence([Type Étudiant])

One problem is if [Type Étudiant] is ever null then your function will error.
So you can call it by adding a space within the call or changing the variable
type to Variant instead of string or testing the value of [Type Étudiant]
Before making the call to the function.

Equivalence([Type Étudiant] & "")
Or
IIF(IsNull([Type Étudiant]),Null,Equivalence([Type Étudiant]))

Or change the Function to
Function Equivalence(StudentType As Variant) As String

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am still "fighting" with Select Case!
The following code would work if there were no pase between "Type" and
"Étudiant".
Access says "Compilation Error" "Waiting for: Identificator"

Option Compare Database
Function Equivalence([Type Étudiant] As String) As String
Select Case [Type Étudiant]
Case "CB"
Equivalence = "Belgique"
Case "CF"
Equivalence = "Farel"
Case "CT"
Equivalence = "Tahiti"
End Select
End Function




John Spencer said:
That cries out for a translation table - Two Columns
StudentType and CampusName
One record for each abbreviation and campus name

Then you join the the translation table to your table in the query on
the StudentType fields and get the associated campus name.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Here it is:

=Switch(
[StudentType]="CB";"Campus Belgium";
[StudentType]="CF";"Campus Farel";
[StudentType]="CT";"Campus Tahiti";
.
)

It seems I'll need to use a Function like Select Case, but how would
be the code.

Thanks again



"Jeff Boyce" <[email protected]> a écrit dans le message de
If you'll offer more specific description of what you're using
Switch to accomplish, folks here may be able to offer more specific
suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

In my case, the Switch fonction in the source control of a textbox
accepted only 15 arguments. At the 16th, Access says "Too complex
expression."
Suggestions for a workaround?
Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much.

Telesphore

John Spencer said:
Arguments to a function are variables. Variable names cannot contain
spaces.


Your function might look like the following.
Option Compare Database
Function Equivalence(StudentType As String) As String
Select Case StudentType
Case "CB"
Equivalence = "Belgique"
Case "CF"
Equivalence = "Farel"
Case "CT"
Equivalence = "Tahiti"
End Select
End Function

To call that function, you would use the expression
Equivalence([Type Étudiant])

One problem is if [Type Étudiant] is ever null then your function will
error.
So you can call it by adding a space within the call or changing the
variable type to Variant instead of string or testing the value of [Type
Étudiant] Before making the call to the function.

Equivalence([Type Étudiant] & "")
Or
IIF(IsNull([Type Étudiant]),Null,Equivalence([Type Étudiant]))

Or change the Function to
Function Equivalence(StudentType As Variant) As String

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I am still "fighting" with Select Case!
The following code would work if there were no pase between "Type" and
"Étudiant".
Access says "Compilation Error" "Waiting for: Identificator"

Option Compare Database
Function Equivalence([Type Étudiant] As String) As String
Select Case [Type Étudiant]
Case "CB"
Equivalence = "Belgique"
Case "CF"
Equivalence = "Farel"
Case "CT"
Equivalence = "Tahiti"
End Select
End Function




John Spencer said:
That cries out for a translation table - Two Columns
StudentType and CampusName
One record for each abbreviation and campus name

Then you join the the translation table to your table in the query on
the StudentType fields and get the associated campus name.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Telesphore wrote:
Here it is:

=Switch(
[StudentType]="CB";"Campus Belgium";
[StudentType]="CF";"Campus Farel";
[StudentType]="CT";"Campus Tahiti";
.
)

It seems I'll need to use a Function like Select Case, but how would be
the code.

Thanks again



"Jeff Boyce" <[email protected]> a écrit dans le message de
If you'll offer more specific description of what you're using Switch
to accomplish, folks here may be able to offer more specific
suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

In my case, the Switch fonction in the source control of a textbox
accepted only 15 arguments. At the 16th, Access says "Too complex
expression."
Suggestions for a workaround?
Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top