Microsoft Access SQL query

Question

Can someone please help me out with a 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. Any help would be useful!

Answer 1

Use this sintax in access never fails

(this is an example)

select * from customer, adress where customer.id = adress.customerId

Answer 2

Access uses different wildcard patterns.
In your case, it will be - LIKE '?Flood?' (replace question mark with asterisk).

I don't know formatting codes to apply here so that it shows it correctly.
See the link for details - http://www.techonthenet.com/access/queries/like.php

Answer 3

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.

Answer 4

The syntax error is caused because access uses " instead of ' as the string delimiter. As previously mentioned you will also need to change the % wildcards to *