Syntax error (missing operator) in Access 2007

Question

Hello,

I'm new to microsoft access and I'm trying to create age groups in a database containing patient data. The data table is called 'Sheet1'. Each patient is identified by a Patient-ID, which can show up several times in the database, depending on the amount of visits. 

So far, I've done a query (Sheet1 Query) extracting each unique patient, with corresponding age (Leeftijd) and gender (Geslacht). I'm trying to create the different age groups from this query, using this: 

SELECT SUM (CASE WHEN [Leeftijd] < 18 THEN 1 ELSE 0 END) AS [Under 18]

             SUM (CASE WHEN [Leeftijd] BETWEEN 18 AND 35 THEN 1 ELSE 0 END) AS [18-35]

FROM [Sheet1 Query]

Now, I keep running into "Syntax error (missing operator) in query expression 'SUM (CASE WHEN [Leeftijd]< 18 THEN 1 ELSE 0 END)'.

Any help is appreciated,

Menjo

Answer 1

Instead of the standard SQL CASE expression call the VBA IIF function:

SELECT SUM(IIF([Leeftijd] < 18,1,0)) AS [Under 18],

             SUM(IIF([Leeftijd] BETWEEN 18 AND 35,1,0)) AS [18-35]

FROM [Sheet1 Query];

Is the Leeftijd colum a computed column retuned by the Sheet1 Query query, or is it a column in a base table?  If the latter the values will of course become incorrect over time unless the age is intended to be that at a specific point in time rather than the current age.  The latter is best returned by computing the age on the basis of the person's date of birth, using a function such as that below:

Public Function GetAge(varDoB As Variant, Optional varAgeAt As Variant) As Variant

    If IsMissing(varAgeAt) Then varAgeAt = VBA.Date

    GetAge = DateDiff("yyyy", varDoB, varAgeAt) - _

        IIf(Format(varAgeAt, "mmdd") < Format(varDoB, "mmdd"), 1, 0)

End Function

By default this returns the age in years at the current date, but can return the age at a specific date by passing the latter into the function as the optional varAgeAt argument.

Answer 2

It's simply that the CASE expression, while part of the SQL standard and supported by client server products like SQL Server or Oracle, is not supported by JET/ACE SQL as used by Access.  The nearest equivalent is the VBA IIF function.  VBA is the language used in Access for writing functions or sub-procedures, and most of its functions can be used in a query or SQL statement in Access.

Answer 3

Thank you, that solved the problem. If you have the time, could you perhaps elaborate a bit on why it went wrong in the original code. It would be helpful to me, as I'm trying to learn SQL. 

The leeftijd column is computed by the query. It is intended to be at that specific point in time, so that should be fine. 

Thanks again,

Menjo

Answer 4

CASE WHEN is valid in SQL Server, not in Access. You could use

SELECT Abs(Sum([Leeftijd]<18])) AS {under 18], Abs(Sum([Leeftijd] Between 18 And 35)) AS [18-35]

FROM [Sheet1 Query]

Explanation: [Leeftijd]<18] returns either True = -1 or False = 0.

So when you sum [Leeftijd]<18, each patient younger than 18 years contributes -1 to the sum.

To get rid of the -, we use the Abs function.

Answer 5

Hi,

try with

SELECT SUM (IIF([Leeftijd] < 18,1,0)) AS [Under 18]

             SUM (IIF([Leeftijd] >=18 And [Leeftijd]<=35 ,1,0)) AS [18-35]

FROM [Sheet1 Query]

Mimmo