CODEWITHSUNDEEP
sqlms-accesssyntax-errorisnulliif-function
Dm3k1
Dm3k1

Reputation: 187

syntax error SQL Access Nested IIF ISNULL statements

I'm getting a syntax error, I've changed it multiple times, re-typed it and tried to draw a map even. Now after my head is about to expload, out of desperation I'm turning to someone who has an eye for this.

I might be pushing it trying to grab distinct counts of the iif statements, but probably this question is less technical and more about having an eye...

I don't get problems with the FROM/WHERE clauses but they are provided for additional reference.

Please help me if I'm doing something totally wrong:

(SELECT DISTINCT(COUNT(IIF(ISNULL(CombineTables.[Product Description]),

IIF(ISNULL(CombineTables.[Product Number (OEM)]),


IIF(ISNULL(CombineTables.[Product Number (under supplier PA)],"Incomplete Data",CombineTables.[Product Number (under supplier PA)])),

CombineTables.[Product Number (OEM)]),

CombineTables.[Product Description])))
FROM CombineTables WHERE [PA#]=Forms!PADiagnosticsForm!Combo2 AND "QTR." & " " & CombineTables.Quarter & "-" & CombineTables.Year=Forms!PADiagnosticsForm!List68)

FROM CombineTables
WHERE CombineTables.[Price Agreement Price]*CombineTables.Quantity-CombineTables.[Total Extended Price]<>0 And CombineTables.[PA#]=Forms!PADiagnosticsForm!Combo2 And "QTR." & " " & CombineTables.Quarter & "-" & CombineTables.Year=Forms!PADiagnosticsForm!List68;

I thank you in advance.

Upvotes: 0

Views: 1442

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

There are several things wrong with your query:

  1. It is incomplete. The ( before the first SELECT makes it an expression before the second FROM CombineTables. It's therefore missing a leading SELECT. If that's not your full query, then it doesn't help people trying to help you.
  2. NZ(A,B) is identical to and more concise thanIIF(ISNULL(A, B, A))`
  3. Your SELECT COUNT pattern is suspicious especially with all the IIF(ISNULLs. Because in effect, what is being COUNT-ed is never NULL, so you might as well rewrite the entire block in side the COUNT as COUNT(1). COUNT unlike SUM does not care about the actual values being counted, it only cares about whether the value is NULL (counted as 0, anything non-NULL counts as 1).

Upvotes: 1

Related Questions