CODEWITHSUNDEEP
sqlms-accessif-statementnullcase
Frithiof
Frithiof

Reputation: 101

SQL CASE statement IS NULL syntax error

I'm very new to SQL and I'm trying to do an IF-statement. Here is my code, hopefully you will be able to spot where the problem is. Thanks in advance! 

SELECT [data1].[Last Name]+", "+[First Name] AS Name, 
       [data1].[User ID], 
       (CASE WHEN [Kla] IS null THEN [Nr] ELSE [Kla]+"-"+[Nr] END) AS [Course Code] 
FROM [data1], [data2]

Syntax error (operator missing) '(CASE WHEN [Kla] IS null THEN [Nr] ELSE [Kla]+"-"+[Nr] END)'

I'm running this in Microsoft Access from two tables (data1 and data2) which I imported from Excel

Upvotes: 1

Views: 2904

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

Try this:

SELECT [data1].[Last Name]+", "+[First Name] AS Name, 
[data1].[User ID], 
IIF(ISNULL([Kla]),[Nr],[Kla]+"-"+[Nr]) AS [Course Code]
FROM [data1], [data2]

AFAIK, you cannot include NULL checks in a CASE statement in Access, so I would suggest you use IIF instead.

Upvotes: 2

Related Questions