query issue in access

i have this query but it is showing me an error when i run it, it seems ok to me

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

Error i am getting is:

Error Executing Database Query.

Syntax error (missing operator) in query expression '1=1 LEFT JOIN Categories ON contactDetails.categoryid = Categories.catID LEFT join titles on titles.titleID = contactDetails.titles AND Activated = ?'
LVL 16
Gurpreet Singh RandhawaWeb DeveloperAsked:
Who is Participating?
 
OCDanCommented:
Here you are mate, works for me at least.

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 = (param 1)
ORDER BY contactID DESC
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
0
 
OCDanCommented:
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.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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'.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
yeah, Braces did work, don no why issue with access
0
 
plummetCommented:
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

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

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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.