Access giving error in SQL view but query runs correctly

Question

I have a query in Access which is returning data from two tables. The one table needs to be filtered however as I need to return the most recent entry for a particular date.

I have created the query by copying the SQL from another query and then modifying it in the design view. When I save the query from design view and run it I get the expected outcome of a three column table with totals for each ShiftType for each StaffNumber. However when I move the query over to my VBA application it throws an error. Also when I view the query in SQL view and modify it (add and then remove whitespace) it throws an error.

The query SQL is:

SELECT DataLeave.StaffNumber, TABLE1.ShiftType, Count(TABLE1.ShiftType) AS CountOfShiftType
FROM DataLeave INNER JOIN [SELECT DataShifts.StaffNumber, ShiftType, DataShifts.ShiftDate 
FROM DataShifts 
INNER JOIN [SELECT StaffNumber, ShiftDate, MAX(ID) AS IDMAX FROM DataShifts GROUP BY DataShifts.StaffNumber,DataShifts.ShiftDate]. AS FILTER ON DataShifts.ID = FILTER.IDMAX]. AS TABLE1 ON (DataLeave.LeaveDate = TABLE1.ShiftDate) AND (DataLeave.StaffNumber = TABLE1.StaffNumber)
WHERE (((DataLeave.Active)=True) AND ((DataLeave.LeaveDate) Between #9/3/2013# And #3/15/2014#) AND ((DataLeave.StaffNumber)='2537860')) OR (((DataLeave.StaffNumber)='2524710')) OR (((DataLeave.StaffNumber)='2515610') AND ((DataLeave.LeaveTypeShort) Like '*FD'))
GROUP BY DataLeave.StaffNumber, TABLE1.ShiftType
ORDER BY DataLeave.StaffNumber;

The error that I get is:

Syntax error in query experssion 'DataShifts.ID = FILTER.IDMAX]. AS TABLE1 ON (DataLeave.LeaveDate = TABLE1.ShiftDate)'

I have designed another query which works using IN but it is extremely slow so I would like to get this working but no amount of tweaking so far has worked.

The VBA code is:

q = "SELECT DataLeave.StaffNumber, TABLE1.ShiftType, Count(TABLE1.ShiftType) AS CountOfShiftType "
q = q + "FROM DataLeave INNER JOIN [SELECT DataShifts.StaffNumber, ShiftType, DataShifts.ShiftDate "
q = q + "FROM DataShifts "
q = q + "INNER JOIN [SELECT StaffNumber, ShiftDate, MAX(ID) AS IDMAX FROM DataShifts GROUP BY DataShifts.StaffNumber,DataShifts.ShiftDate]. AS FILTER ON DataShifts.ID = FILTER.IDMAX]. AS TABLE1 ON (DataLeave.LeaveDate = TABLE1.ShiftDate) AND (DataLeave.StaffNumber = TABLE1.StaffNumber) "
q = q + "WHERE (((DataLeave.Active)=True) AND ((DataLeave.LeaveDate) Between #9/3/2013# And #3/15/2014#) AND ((DataLeave.StaffNumber)='2537860')) OR (((DataLeave.StaffNumber)='2524710')) OR (((DataLeave.StaffNumber)='2515610') AND ((DataLeave.LeaveTypeShort) Like '*FD')) "
q = q + "GROUP BY DataLeave.StaffNumber, TABLE1.ShiftType "
q = q + "ORDER BY DataLeave.StaffNumber;"

Set Rs = Db.OpenRecordset(q)

using DAO

HansUp HansUp · Accepted Answer · 2014-03-16T21:17:46

Access SQL accepts 2 styles of bracketing for subqueries:

[statement]. AS alias
(statement) AS alias

Either can work, but the first form can break when the subquery statement includes square brackets. Since your first subquery includes another subquery contained in an additional pair of square brackets, I think that may be the cause of the problem.

Also, you have a reserved word, FILTER as an alias. That might create another problem later.

I suggest you save this SQL as a new Access query, qryFilter:

SELECT
    DataShifts.StaffNumber,
    DataShifts.ShiftDate,
    MAX(DataShifts.ID) AS IDMAX
FROM DataShifts
GROUP BY
    DataShifts.StaffNumber,
    DataShifts.ShiftDate

Then you can revise the main query to use qryFilter instead of a subquery statement.

SELECT
    dl.StaffNumber,
    t1.ShiftType,
    Count(t1.ShiftType) AS CountOfShiftType
FROM
    DataLeave AS dl
    INNER JOIN
        (
            SELECT
                ds.StaffNumber,
                ds.ShiftType,
                ds.ShiftDate 
            FROM
                DataShifts AS ds
                INNER JOIN qryFilter AS fltr
                ON ds.ID = fltr.IDMAX
        ) AS t1
    ON
            (dl.LeaveDate = t1.ShiftDate)
        AND (dl.StaffNumber = t1.StaffNumber)
WHERE
        dl.Active=True
    AND dl.LeaveDate Between #9/3/2013# And #3/15/2014#
    AND dl.StaffNumber IN ('2537860', '2524710', '2515610')
    AND dl.LeaveTypeShort Like '*FD'
GROUP BY
    dl.StaffNumber,
    t1.ShiftType
ORDER BY dl.StaffNumber;

Notes:

I lost track of the WHERE clause logic, so you may need to fix that if I got it wrong.
I used the second style, parentheses, for the subquery. If you modify the query in the query designer from Access 2003, it may (probably will) switch those to square brackets. But, since there will then be only one pair of square brackets, it should not break the query.
Finally, you may be able to make your original statement work if you use parentheses instead of square brackets around each subquery statement. Then just make sure to switch back to parentheses every time the Access 2003 query designer switches to square brackets. (Be careful!)

Access giving error in SQL view but query runs correctly

2 Answers