SWITCH Incorrect Argument Amount - Can't See Where

Question

I'm trying to use the SWITCH function in MS Access and I keep getting told there aren't the right number of arguments. I'm new to the switch function but I understand the syntax.

Switch ( expression1, value1, expression2, value2, ... expression_n, value_n )

Am I doing something in the switch that can't be used, the sub-queries maybe? I've checked my commas, parentheses and that there is an expression and then a return value. Driving me crazy as I'm converting from T-SQL to jet and replacing a CASE statement that worked perfectly fine.

Switch(
        (eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND eh.STARTDTE < sh.STARTDTE),
          "FAIL - Employment Start Date Before Service Start Date",
        (eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND eh.ENDDTE > sh.ENDDTE),
          "FAIL - Employment End Date After Service End Date",
        (eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND  (
                                                                                            SELECT
                                                                                                COUNT(*)
                                                                                            FROM
                                                                                                emphist AS eh2
                                                                                            WHERE
                                                                                                eh2.MEMBNO = sh.MEMBNO
                                                                                            AND eh2.EMPID = sh.EMPID
                                                                                            AND eh2.ENDDTE = DateAdd(dd,-1,eh.STARTDTE)
                                                                                          ) = 0),
          "FAIL - Previous Employment Period Corrupt or Missing",
        (eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND  (
                                                                                            SELECT
                                                                                                COUNT(*)
                                                                                            FROM
                                                                                                emphist AS eh2
                                                                                            WHERE
                                                                                                eh2.MEMBNO = sh.MEMBNo
                                                                                            AND eh2.EMPID = sh.EMPID
                                                                                            AND eh2.ENDDTE = DateAdd(dd,-1,eh.STARTDTE)
                                                                                          ) = 0),
          "FAIL - Previous Employment Period Corrupt or Missing",
        (eh.STARTDTE <> sh.STARTDTE AND IsNull(eh.ENDDTE,"") <> IsNull(sh.ENDDTE,"") AND (
                                                                                            SELECT
                                                                                                COUNT(*)
                                                                                            FROM
                                                                                                emphist AS eh2
                                                                                            WHERE
                                                                                                eh2.MEMBNO = sh.MEMBNO
                                                                                            AND eh2.EMPID = sh.EMPID
                                                                                            AND eh2.STARTDTE = DateAdd(dd,1,eh.ENDDTE)
                                                                                          ) = 0),
          "FAIL - Next Employment Period Corrupt or Missing",
        (eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE <> sh.ENDDTE AND (
                                                                    SELECT
                                                                        COUNT(*)
                                                                    FROM
                                                                        emphist AS eh2
                                                                    WHERE
                                                                        eh2.MEMBNO = sh.MEMBNO
                                                                    AND eh2.EMPID = sh.EMPID
                                                                    AND eh2.STARTDTE = DateAdd(dd,1,eh.ENDDTE)
                                                                  ) = 0),
          "FAIL - Next Employment Period Corrupt or Missing",
        (eh.STARTDTE <> sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND (
                                                                    SELECT
                                                                        COUNT(*)
                                                                    FROM
                                                                        emphist AS eh2
                                                                    WHERE
                                                                        eh2.MEMBNO = sh.MEMBNO
                                                                    AND eh2.EMPID = sh.EMPID
                                                                    AND eh2.ENDDTE = DateAdd(dd,-1,eh.STARTDTE)
                                                                  ) = 0),
          "FAIL - Previous Employment Period Corrupt or Missing",
        (eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND  (
                                                                    SELECT
                                                                        COUNT(*)
                                                                    FROM
                                                                        emphist AS eh2
                                                                    WHERE
                                                                        eh2.MEMBNO = sh.MEMBNO
                                                                    AND eh2.EMPID = sh.EMPID
                                                                  ) <> 1),
          "FAIL - Too Many or No Employment History Records"
  ) AS "Reason"

UPDATED WITH ANSWERS

Switch(
        (eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND eh.STARTDTE < sh.STARTDTE),
          "FAIL - Employment Start Date Before Service Start Date",
        (eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND eh.ENDDTE > sh.ENDDTE),
          "FAIL - Employment End Date After Service End Date",
        (eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND  (
                                                                                            SELECT
                                                                                                COUNT(*)
                                                                                            FROM
                                                                                                emphist AS eh2
                                                                                            WHERE
                                                                                                eh2.MEMBNO = sh.MEMBNO
                                                                                            AND eh2.EMPID = sh.EMPID
                                                                                            AND eh2.ENDDTE = DateAdd("dd",-1,eh.STARTDTE)
                                                                                          ) = 0),
          "FAIL - Previous Employment Period Corrupt or Missing",
        (eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND  (
                                                                                            SELECT
                                                                                                COUNT(*)
                                                                                            FROM
                                                                                                emphist AS eh2
                                                                                            WHERE
                                                                                                eh2.MEMBNO = sh.MEMBNo
                                                                                            AND eh2.EMPID = sh.EMPID
                                                                                            AND eh2.ENDDTE = DateAdd("dd",-1,eh.STARTDTE)
                                                                                          ) = 0),
          "FAIL - Previous Employment Period Corrupt or Missing",
        (eh.STARTDTE <> sh.STARTDTE AND NZ(eh.ENDDTE,"") <> NZ(sh.ENDDTE,"") AND (
                                                                                            SELECT
                                                                                                COUNT(*)
                                                                                            FROM
                                                                                                emphist AS eh2
                                                                                            WHERE
                                                                                                eh2.MEMBNO = sh.MEMBNO
                                                                                            AND eh2.EMPID = sh.EMPID
                                                                                            AND eh2.STARTDTE = DateAdd("dd",1,eh.ENDDTE)
                                                                                          ) = 0),
          "FAIL - Next Employment Period Corrupt or Missing",
        (eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE <> sh.ENDDTE AND (
                                                                    SELECT
                                                                        COUNT(*)
                                                                    FROM
                                                                        emphist AS eh2
                                                                    WHERE
                                                                        eh2.MEMBNO = sh.MEMBNO
                                                                    AND eh2.EMPID = sh.EMPID
                                                                    AND eh2.STARTDTE = DateAdd("dd",1,eh.ENDDTE)
                                                                  ) = 0),
          "FAIL - Next Employment Period Corrupt or Missing",
        (eh.STARTDTE <> sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND (
                                                                    SELECT
                                                                        COUNT(*)
                                                                    FROM
                                                                        emphist AS eh2
                                                                    WHERE
                                                                        eh2.MEMBNO = sh.MEMBNO
                                                                    AND eh2.EMPID = sh.EMPID
                                                                    AND eh2.ENDDTE = DateAdd("dd",-1,eh.STARTDTE)
                                                                  ) = 0),
          "FAIL - Previous Employment Period Corrupt or Missing",
        (eh.STARTDTE = sh.STARTDTE AND eh.ENDDTE = sh.ENDDTE AND  (
                                                                    SELECT
                                                                        COUNT(*)
                                                                    FROM
                                                                        emphist AS eh2
                                                                    WHERE
                                                                        eh2.MEMBNO = sh.MEMBNO
                                                                    AND eh2.EMPID = sh.EMPID
                                                                  ) <> 1),
          "FAIL - Too Many or No Employment History Records"
  ) AS "Reason"

That seems valid to me. Where are you using this? (directly in SQL, query builder, expression)? — Erik A, Oct 31 '17 at 21:10
Thanks for having a look over it Erik. I'm writing a SELECT INTO query in SQL view. I just wanted someone else to check over this switch as Access is complaining about it but I'm guessing the problem is elsewhere because it definitely has the right number of arguments. I'm going to spend some more time on it today and if I can't solve it I'll post the whole query. — Boltie, Nov 01 '17 at 08:38

score 0 · Accepted Answer · answered Nov 01 '17 at 10:14

0

It is not Switch but IsNull that raises the error. So replace all these faulty statements:

IsNull(eh.ENDDTE,"")

with:

Nz(eh.ENDDTE)

answered Nov 01 '17 at 10:14

Gustav

53,498
7
29
55

Cheers Gustav, I eventually realised. Doh! I replaced with NZ but I also left the second return argument of "". Is there a reason you left it out? – Boltie Nov 01 '17 at 10:39
It's the default string return value for Null, so you don't have to specify it. – Gustav Nov 01 '17 at 10:43
Makes sense. Thanks again for taking the time to look and comment. – Boltie Nov 01 '17 at 10:46

score 0 · Answer 2 · answered Nov 01 '17 at 10:37

OK, there were two issues.

The IsNull function isn't an Access function so I replaced with NZ (same syntax).
For the DateAdd function, I had neglected to surround the datepart argument with speech marks.

The query now happily runs. I'm guessing as the IsNull function wasn't recognised, it appeared as though the argument was missing which I suppose it was. Lesson learnt, incorrect number of arguments could also indicate invalid functions.

NB. I will update my OP with the amended switch statement for others to compare.

SWITCH Incorrect Argument Amount - Can't See Where

2 Answers2