What Is The Correct Case Select Statement in Access 2010

What is the correct CASE SELECT Statement in Access 2010?

Figured it out:

Access 2010 does not have the CASE function, we use SWITCH instead.

SELECT user_id, username, first_name, middle_name, last_name, SWITCH(is_enable=True,'Yes',is_enable=False,'No')
FROM tbl_user_accounts ORDER BY user_id

Thanks to chuff and JW.

MS Access 2010 Module Case Statement

Try this:

Option Explicit

Public Function whichChineseZodiacSign(Birthday As Date) As String

    Select Case Birthday
    Case #2/18/1958# To #2/7/1959#
        whichChineseZodiacSign = "Dog"
    Case Else
        whichChineseZodiacSign = "Unknown"
    End Select

End Function

In your case you had false condition in the Select Case.

What is the equivalent of Select Case in Access SQL?

Consider the Switch Function as an alternative to multiple IIf() expressions. It will return the value from the first expression/value pair where the expression evaluates as True, and ignore any remaining pairs. The concept is similar to the SELECT ... CASE approach you referenced but which is not available in Access SQL.

If you want to display a calculated field as commission:

SELECT
    Switch(
        OpeningBalance < 5001, 20,
        OpeningBalance < 10001, 30,
        OpeningBalance < 20001, 40,
        OpeningBalance >= 20001, 50
        ) AS commission
FROM YourTable;

If you want to store that calculated value to a field named commission:

UPDATE YourTable
SET commission =
    Switch(
        OpeningBalance < 5001, 20,
        OpeningBalance < 10001, 30,
        OpeningBalance < 20001, 40,
        OpeningBalance >= 20001, 50
        );

Either way, see whether you find Switch() easier to understand and manage. Multiple IIf()s can become mind-boggling as the number of conditions grows.

Access 2010 VBA Select Case Statement

The function itself is fine.

However the problem will be in the way you are calling the function.

In VBA, function arguments are passed by reference (ByRef) by default. This means that the data types in the caller have to be exactly the same as those in the function.

Two choices:

1) Adjust your function to

Public Function MyRateCalc(ByVal rateType As String, ByVal fixedAmount As Long, ByVal minAmount As Long, ByVal rateDollar As Long, ByVal valPerc As Long, ByVal rtValue As Long) As Double

2) Check the caller variable types carefully; they must match exactly.

I prefer approach (1) as it increases program stability (functions cannot unintentionally modify variables in the calling code); even though it's at the expense of a string copy in your case.

Microsoft Access - Case Query

There is no CASE ... WHEN in Access SQL. You can use the Switch Function instead.

UPDATE HAI
SET REGION = Switch(
    NUMREG Like '*1', 'BDG',
    NUMREG Like '*2', 'JKT',
    NUMREG Like '*3', 'KNG'
    );

That query uses Access' default (ANSI 89 mode) * instead of % wildcard character. If you want to use the % wildcard, you can do it with the ALike comparison operator.

UPDATE HAI
SET REGION = Switch(
    NUMREG ALike '%1', 'BDG',
    NUMREG ALike '%2', 'JKT',
    NUMREG ALike '%3', 'KNG'
    );

Case expressions in Access

You can use the IIF() function instead.

IIF(condition, valueiftrue, valueiffalse)

• condition is the value that you want to test.

• valueiftrue is the value that is returned if condition evaluates to TRUE.

• valueiffalse is the value that is returned if condition evaluates to FALSE.

There is also the Switch function which is easier to use and understand when you have multiple conditions to test:

Switch( expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )

The Switch function argument list consists of pairs of expressions and
values. The expressions are evaluated from left to right, and the
value associated with the first expression to evaluate to True is
returned. If the parts aren't properly paired, a run-time error
occurs. For example, if expr-1 is True, Switch returns value-1. If
expr-1 is False, but expr-2 is True, Switch returns value-2, and so
on.

Switch returns a Null value if:

• None of the expressions is True.

• The first True expression has a corresponding value that is Null.

NOTE: Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for
undesirable side effects. For example, if the evaluation of any
expression results in a division by zero error, an error occurs.

MS Access Query with CASE statement

MS Access does not support CASE statements. Use IIF:

IIF(tsl.activitat <> '', tsl.activitat, ts.activitat ) AS Activitat

I'm not sure if Access supports aliases on LEFT JOIN either, but it probably does

Note (although correctly tagged), the title of your question might be troublesome... most people refer to Transact-SQL (TSQL) when writing "MS SQL". Transact-SQL is used by MS SQL Server, but not by MS Access, which uses its own SQL dialect (called "Access SQL", and pretty limited in comparison)

Case Statement Not Evaluating How I expect it to MS Access VBA

1) use Option Explicit

2) declare variables

3) vba Case statement doesn't work like that. Your statement looks a little bit like SQL Server syntax and a lot wrong.

4) trick for young players: if you want to know how many records in your recordset then you need to access all the records.

5) [not shown] consider rewriting your SQL statements as "select count(*) as recCount from [Dual Year Carrier Report] where ..." and checking the value of rs1("recCount") , rs2("recCount"), etc.

Option Compare Database
Option Explicit

Sub CheckDataTypes()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, rs4 As DAO.Recordset, rs5 As DAO.Recordset

    Set db = CurrentDb

    Set rs1 = db.OpenRecordset("SELECT [Dual Year Carrier Report].EE_ID FROM [Dual Year Carrier Report] WHERE ((([Dual Year Carrier Report].EE_ID) Is Not Null));")
    If Not rs1.EOF Then rs1.MoveLast    ' required to force MS Access to load records to recordset NOW
    Set rs2 = db.OpenRecordset("SELECT [Dual Year Carrier Report].PERSON_OID FROM [Dual Year Carrier Report] WHERE ((([Dual Year Carrier Report].PERSON_OID) Is Not Null));")
    If Not rs2.EOF Then rs2.MoveLast
    Set rs3 = db.OpenRecordset("SELECT [Dual Year Carrier Report].SPONSOR_OID FROM [Dual Year Carrier Report] WHERE ((([Dual Year Carrier Report].SPONSOR_OID) Is Not Null));")
    If Not rs3.EOF Then rs3.MoveLast
    Set rs4 = db.OpenRecordset("SELECT [Dual Year Carrier Report].ZIP_CD FROM [Dual Year Carrier Report] WHERE ((([Dual Year Carrier Report].ZIP_CD) Is Not Null));")
    If Not rs4.EOF Then rs4.MoveLast
    Set rs5 = db.OpenRecordset("SELECT [Dual Year Carrier Report].EP_PERSON_OID FROM [Dual Year Carrier Report] WHERE ((([Dual Year Carrier Report].EP_PERSON_OID) Is Not Null));")
    If Not rs5.EOF Then rs5.MoveLast

    Debug.Print rs1.RecordCount & ", " & rs2.RecordCount & ", " & rs3.RecordCount & ", " & rs4.RecordCount & ", " & rs5.RecordCount

    If rs1.RecordCount = 0 Then
        Debug.Print rs1.RecordCount
        MsgBox ("Please import Dual Year Carrier Report again and make sure to change the proper fields to text data type")
    ElseIf rs2.RecordCount = 0 Then
        MsgBox ("Please import Dual Year Carrier Report again and make sure to change the proper fields to text data type")
    ElseIf rs3.RecordCount = 0 Then
        MsgBox ("Please import Dual Year Carrier Report again and make sure to change the proper fields to text data type")
    ElseIf rs4.RecordCount = 0 Then
        MsgBox ("Please import Dual Year Carrier Report again and make sure to change the proper fields to text data type")
    ElseIf rs5.RecordCount = 0 Then
        MsgBox ("Please import Dual Year Carrier Report again and make sure to change the proper fields to text data type")
    End If

    Debug.Print rs1.RecordCount & ", " & rs2.RecordCount & ", " & rs3.RecordCount & ", " & rs4.RecordCount & ", " & rs5.RecordCount

End Sub

Hope this helps,
GraemeR

---

Related Topics