[RESOLVED] MS Access SQL Question ...

[TheBionicOrange]

I am using MS Access 2000.

Why does this query give me a syntax error ?
All field names are correct, and both joins should be "inner".

select a.QUESTION_GROUP,a.QUESTION_ID,a.WEIGHTING,a.CONTROL_MEASURE_OWNER,a.CONTROL_MEASURE_APPROVED,a.COMPLETION_TIMESCALE,b.CONTROL_MEASURE,c.QUESTION
from AUDIT_LOCATION_QUESTION_RESULTS a 
inner join AUDIT_CONTROL_MEASURES b on (a.CHAIN=b.CHAIN and a.QUESTION_GROUP=b.QUESTION_GROUP and a.QUESTION_ID=b.QUESTION_ID) 
inner join AUDIT_GROUP_QUESTIONS c on (a.CHAIN=c.CHAIN and a.QUESTION_GROUP=c.QUESTION_GROUP and a.QUESTION_ID=c.QUESTION_ID)
where a.CHAIN = '01' 
and a.BRANCH_NUMBER = '0001' 
and a.AUDIT_DATE = '20060502' 
order by a.QUESTION_GROUP,a.QUESTION_ID

[DKenny]

If AUDIT_DATE is a Date type Field, you should change its WHERE condition to

a.AUDIT_DATE = #02/05/2006#

i.e. replace the single quotes with hash marks and add /'s.

[TheBionicOrange]

It isn't. Sorry should have stated that. They are strings, so I store them as YYYYMMDD.

The error I get is :

[si_the_geek]

I think it's the brackets, Access has an unusual way of using them. It may need to be like this:

from (AUDIT_LOCATION_QUESTION_RESULTS a 
inner join AUDIT_CONTROL_MEASURES b on a.CHAIN=b.CHAIN ... and a.QUESTION_ID=b.QUESTION_ID) 
inner join AUDIT_GROUP_QUESTIONS c on a.CHAIN=c.CHAIN ... and a.QUESTION_ID=c.QUESTION_ID

[TheBionicOrange]

Si you genius .. thats EXACTLY what it was. Works a treat now.
I'm so used to SQL Server I couldn't get my head round what to me looks like a weird weird syntax that MS Access uses sometimes .... this being a case in point.

I was looking at that for over an hour yesterday ... and would never have thought to do it THAT way !

Your a life-saver ... thanks a lot

[TheBionicOrange]

By the way ... I tried rating your post but was told :
"You must spread some Reputation around before giving it to si_the_geek again."

Ironic really .. I seem to HAVE quite a reputation where I work already