Syntax error in tough query 1

[cr84net2]

I can't seem to get the syntax right here. No matter what I try i get either unterminated string constant error or Expected')' error. Any help would be greatly appreciated.

sSqlWrk = "SELECT [wrestlername] FROM [wrestler]"
sSqlWrk = sSqlWrk & " WHERE ([weightclass] IN (@FILTER_VALUE))"
sSqlWrk = sSqlWrk & " AND (" & NOT EXISTS (" & SELECT [wrestlername] FROM lteam WHERE [ltID]= " & lteam.ltID.CurrentValue & ") & ")"

The error comes at the end of the subquery where the ") & ")" is. Thanks for any advice here.

 

[johnwm]

Not sure what your query is trying to achieve, but you have unmatched pairs of quote marks and 'NOT EXISTS' outside your quote marks.

Have a look at

sSqlWrk = "SELECT [wrestlername] FROM [wrestler]"
sSqlWrk = sSqlWrk & " WHERE ([weightclass] IN (@FILTER_VALUE))"
sSqlWrk = sSqlWrk & " AND (NOT EXISTS ( SELECT [wrestlername] FROM lteam WHERE [ltID]= " & lteam.ltID.CurrentValue & "))"

I generally stick this sort of stuff in a vbs script and run it from the desktop to make debugging easy. Just add a MsgBox at the end, then REM out bits to see exactly where the problem lies.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[cr84net2]

Thanks Johnwm, that stopped the errors. I appreciate your help. Now I am getting another syntax error which I thought I could fix using your example above. This is inherited code where a combo box fills an array from two tables weight class and wrestler. I am trying to modify it so when a wrestler is picked from a weight class he will no longer be available in the combo box. Once picked the wrestlername is placed in a different table which also records team and league ids. So the current league ID is the parameter sinc ethe wrestler needs to be available to other leagues after he is picked in one. I hope this makes sense. This is the error I am getting now. The syntax errors are killing me here.

sSqlWrk = "SELECT [wrestlername], [wrestlername] AS DispFld, [weightclass] FROM [wrestler]"
If lteam.wrestlername.CurrentValue = "" Or IsNull(lteam.wrestlername.CurrentValue) Then
sSqlWrk = sSqlWrk & " WHERE 0=1"
	Else
sSqlWrk = sSqlWrk & " WHERE [wrestlername] = '" & ew_AdjustSql(lteam.wrestlername.CurrentValue) & "'"
	End If
sSqlWrk = sSqlWrk & " AND (NOT EXISTS (SELECT [wrestlername] FROM lteam WHERE lteam.ltID = "& lteam.ltID.CurrentValue & "))"

Irit is the last line again with the )) that is prompting the error, I am getting "Syntax error" column 121 which is immediately after clteam.ltID.Currentvalue. This one is making me bang my head. Thanks again for any guidance.

 

[cr84net2]

I'm sorry my post says a combo box fills an array it should say an array fills a combo box. sorry I missed that on preview. Thanks!

 

[johnwm]

Since this now looks like a SQL problem, try asking in the most appropriate forum:
forum183
forum436
forum701

Do a forum search (top of page) if you're using something else

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints