Problem with SQL code in excel MS Query

[altae81]

Hi,
i've been trying this code in MS Query in excel but it keeps telling me the "SQL command is not properly ended"!...i've been trying so hard to understand where the problem is but no luck so far. The code works perfect in SQL view in MS Access. can someone please help me?

SELECT DISTINCT Copy1.RQN_NO, Copy1.RQN_TITLE, Copy1.RQN_STATUS
FROM R_RQNMAS AS Copy1 LEFT JOIN [SELECT Copy2.RQN_NO FROM R_RQNMAS AS Copy2 WHERE (Copy2.RQN_STATUS="CL" Or Copy2.RQN_STATUS="CN" Or Copy2.RQN_STATUS="RJ")]. AS SQ ON Copy1.RQN_NO=SQ.RQN_NO
WHERE (((SQ.RQN_NO) Is Null));

 

[Ruddles]

I'm not sure you can wrap a subquery in square brackets: use normal parentheses.

Also there's a dot (period) afer the second square bracket which appears to be spurious: remove it.

Which version of Access did your SQL come out of?

 

[Richard Schollar]

Isn't what you've got just doing the following?

SELECT DISTINCT RQN_NO, RQN_TITLE, RQN_STATUS
FROM R_RQNMAS 
WHERE (RQN_STATUS<>'CL' AND RQN_STATUS<>'CN' And RQN_STATUS<>'RJ') And RQN_NO Is Null

No need for a self-referencing join.

 

[altae81]

Hi, thanks for the replies.

To answer Ruddles, the SQL code came out of access 2003. i tried replacing the square brackets and took out the dot but no luck, still wont work and same error message.

To answer Richard, the code you wrote is not what i wanted. Basically, i wanted a sub-query to first remove all items with status closed (CL) or cancelled (CN) etc. and return the results to use in the main query. With your code, the query did not return any records.

The code (and sub-query for my purpose) works perfect in access but somehow the sql is different in excel and wont accept it.

still couldnt get it working, hopefully someone can help!

 

[Ruddles]

I don't know MS Query so this might be a stupid question, but is it a "double quotes inside double quotes" thing - like when you have to double them up when you put them inside a VBA string?

Try replacing the double quotes with apostrophes.

 

[altae81]

Hey Ruddles,
i cant believe it, but you were right! i replaced the double quotes with apostrophes, and hey bingo.............


i cant believe i spent the whole day on this! thanks a million to all...

 

[Richard Schollar]

Just to humour me, do you get the same results with?:

SELECT DISTINCT RQN_NO, RQN_TITLE, RQN_STATUS
FROM R_RQNMAS 
WHERE RQN_NO Is Null

 

[altae81]

Hi Richard,
no, i dont get the same results with your query below.

i run the code as a sub query within the main query...my table lists all status of items, and what i wanted was to have the sub-query first remove all the closed items, and then use the returned data in the main query with other tables. if i run your query below, basically it doesnt return any data because there are no records when RQN_NO is null.

 

[Richard Schollar]

Yes I didn't think thru that SQL well - here's the one it should have been:

SELECT DISTINCT RQN_NO, RQN_TITLE, RQN_STATUS
FROM R_RQNMAS 
WHERE (RQN_STATUS<>'CL' AND RQN_STATUS<>'CN' And RQN_STATUS<>'RJ')

This should be the same as your original because you are returning the records where the RQN_STATus doesn't equal Cl,CN or RJ which is what your self-reference was doing.

 

[Ruddles]

i cant believe it, but you were right!

Oh yes - thanks for sounding surprised!