sqlsql-servert-sqlms-access

Microsoft Access SQL query

In Microsoft SQL Server, I have the following query, which executes fine:

SELECT * FROM ControlPoint
INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID
INNER JOIN Site ON Site.SiteID = Project.SiteID
WHERE Project.ProjectName LIKE '%Flood%'

My problem is, when I try to execute this on Microsoft Access, it gives me some kind of syntax error. It's been forever since I've used Access, but if I remember right, I think the joins need to be in parenthesis or something.

What can I try next?

Solution

  • You will need some parentheses in addition to changing the wild cards:

    SELECT * FROM (ControlPoint
INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID)
INNER JOIN Site ON Site.SiteID = Project.SiteID
WHERE Project.ProjectName LIKE '*Flood*'

    Note that the asterisk is used in the Access query window and with DAO, percent is used with ADO.