SQL WHERE clause being ignored? (Access XP)

Topic

New Reply

I have a WHERE clause that seems to be being ignored. Here are the particulars. I have a multiselect listbox on a form. User selects clients and clicks a command button cmdRemoveClients. The code behind the command button does this stuff:

Private Sub cmdRemoveClients_Click()
Dim strSQL As String ‘holds the delete query string

‘confirm the update here…
””””””””””””””””
If MsgBox(“You are about to remove clients ” & SelectedClients() & ” from the mailing list.”, vbYesNo, “Confirm Delete”) = vbYes Then
strSQL = ” DELETE ClientNoMatch.*, ClientNoMatch.[Client No] ” & _
” FROM ClientNoMatch ” & _
” WHERE SelectedClients() ” & _
” WITH OWNERACCESS OPTION; ”
DoCmd.RunSQL strSQL

Else
‘user chooses not to remove clients…
Exit Sub
End If
End Sub

SelectedClients() is a function that takes the selections in the multiselect listbox and generates a string that looks exactly like this:
“(((ClientNoMatch.[Client No]) = “11128” Or (ClientNoMatch.[Client No]) = “11134”))”

When I click the “Remove Clients” button, the delete query prompts me with the warning message that says “You are about to delete 408 records…. ” 408 records are all of the records in the ClientNoMatch table.
Therefore, I conclude that the SQL Statement is ignoring the WHERE clause that identifies the 2 clients I wish to remove. So, the question is, what do I need to do to the WHERE clause to make it work?
Thanks in advance.

Reply | Quote

Replies

Try putting the function outside the quotes:

strSQL = ” DELETE ClientNoMatch.*, ClientNoMatch.[Client No] ” & _
” FROM ClientNoMatch ” & _
” WHERE ” & SelectedClients & _
” WITH OWNERACCESS OPTION; “

Reply | Quote

works perfectly. Thanks.

Reply | Quote

A comment here which is not about your question
As I understand it the ” WITH OWNERACCESS OPTION; ” only applies to saved queries and not to SQL queries. If so, you will probably have a problem when someone without a sufficient security level tries to run your code.

HTH

Peter

Reply | Quote

good point. This was a SQL statement copied from the SQL View of the Query in Access. I think Access automatically puts that last line it there. I removed it and the query works just fine.
Thanks,

Reply | Quote

Another comment here.

Your statement:
“(((ClientNoMatch.[Client No]) = “11128” Or (ClientNoMatch.[Client No]) = “11134”))”

could better be coded to cut down on the number of characters in the WHERE string. It could look like:

ClientNoMatch.[Client No] in (11128,11134,….etc)

Pat

Reply | Quote

Pat,
Can you tell me the appropriate syntax for the “In” operator? Right now I have a function that returns a string like this:

strItems = “10408W” Or “10001C”

The SQL statement looks like this:
strSQL = “INSERT INTO [ClientNoMatch] ([Client No], [Client Name]) ” & _
“SELECT [Client Master].[Client No], [Client Master].[Client Name] FROM [Client Master]” & _
“WHERE [Client Master].[Client No] In & strItems ”

I get the following error message:
Run time error ‘3075’:
In operator without () in query expression ‘[Client Master].[Client No] In & strItems’.

First, should strItems be enclosed in ( parenthesis )?
Second, should the list of client numbers in strItems be an “Or” statement?

Thanks

Reply | Quote

The IN operator is defined as :

IN (12345,12346,12347,12348) as an example.

Yes, it must be enclosed in round parenthesis.
The list of client numbers must be separated by commas.

HTH
Pat

Reply | Quote

Thanks Pat. These are text fields so I had to enclose them in Chr(34) but it works just fine.

Reply | Quote