query issue in access

Where clause comes last:

SELECT contactDetails.*, categories.catName as MainCat, titles.title FROM contactDetails LEFT JOIN Categories ON contactDetails.categoryid = Categories.catID LEFT join titles on titles.titleID = contactDetails.titles WHERE 1=1 AND Activated = [param 1] ORDER BY contactID DESC

  A nice way to learn SQL in Access is to construct the query in the query designer, then switch to SQL view.  You can see the resulting SQL and cut & paste to where needed.

Jim

oops that was my mistake actual this is the error message i am getting:

Error Executing Database Query.

Syntax error (missing operator) in query expression 'contactDetails.categoryid = Categories.catID LEFT join titles on titles.titleID = contactDetails.titles'.

here it is a change

SELECT contactDetails.*, categories.catName as MainCat ,titles.title FROM contactDetails LEFT JOIN Categories ON contactDetails.categoryid = Categories.catID LEFT join titles on titles.titleID = contactDetails.titles WHERE 1=1 AND Activated = 'True' ORDER BY contactID DESC

SELECT contactDetails.*,
         categories.catName AS MainCat,
         titles.title
FROM contactDetails
              LEFT JOIN Categories ON contactDetails.categoryid = Categories.catID
              LEFT JOIN titles ON titles.titleID = contactDetails.titles
             AND Activated = 'True'
ORDER BY contactID DESC

There is no need for the where clause, 1=1 will always be true, so just leave it out.

i have to apply a where clause so tried clause is:

 SELECT contactDetails.*, categories.catName as MainCat ,titles.title FROM contactDetails LEFT JOIN Categories ON contactDetails.categoryid = Categories.catID LEFT join titles on titles.titleID = contactDetails.titles WHERE Activated = 'True' ORDER BY contactID DESC

The thing is that the statement has <cfif> attached so have to use the where clause

Still getting the clause:

Error Executing Database Query.

Syntax error (missing operator) in query expression 'contactDetails.categoryid = Categories.catID LEFT join titles on titles.titleID = contactDetails.titles'.

yeah, Braces did work, don no why issue with access

Try and cut the query down, see if it works and then add parts back in, eg start with

SELECT 
   contactDetails.*, 
  categories.catName as MainCat 
FROM contactDetails LEFT JOIN Categories 
  ON contactDetails.categoryid = Categories.catID

Select all Open in new window

If that works, then:

SELECT 
   contactDetails.*, 
  categories.catName as MainCat 
FROM contactDetails LEFT JOIN Categories 
  ON contactDetails.categoryid = Categories.catID
LEFT join titles on titles.titleID = contactDetails.titles 
ORDER BY contactID DESC

Select all Open in new window

Are you sure the field names are correct, especially contactDetails.titles? Sure it's not  contactDetails.titleID?

Is Activated a Yes/No (boolean) field? Or a text field?