Search code examples
sqlfunctionms-accesssyntax-error

Syntax error in EXTRACT() query, what operator is missing?

I have a table in MS Access that tracks when members of our pool check in with their pool pass that looks like this:

enter image description here

I'm trying to generate a report that displays the data so I can see how many 'Check-Ins' we've had for each year of operation like so:

Season   Visits
2021     432
2020     123
2019     354
etc.

My plan is to extract the year from the CHECKINTIME, then count the number of unique ID's in that year. I'm using the following SQL for my query:

SELECT 
  EXTRACT(YEAR FROM tblCheckIn.[CHECKINTIME]) AS Season,
  COUNT(tblCheckIn.[ID]) AS Visits
FROM tblCheckIn
GROUP BY (YEAR FROM tblCheckIn.[CHECKINTIME]);

Right now I'm getting the following error: Syntax error (missing operator) in query expression 'EXTRACT(YEAR FROM tblCheckIn.[CHECKINTIME]) I've been pouring over the documentation for EXTRACT() but I can't figure out what I'm doing wrong. I'm guessing it's specifically an MS Access thing?

Solution

  • Ms Access uses a YEAR function and not extract

    SELECT 
  YEAR(tblCheckIn.[CHECKINTIME]) AS Season,
  COUNT(tblCheckIn.[ID]) AS Visits
FROM tblCheckIn
GROUP BY YEAR(tblCheckIn.[CHECKINTIME]);

    or you can use

    SELECT DatePart("yyyy",tblCheckIn.[CHECKINTIME]) AS NewDate FROM tblCheckIn;