Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL WHERE clause being ignored? (Access XP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    Try putting the function outside the quotes:

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

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> A comment here which is not about your question <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    Another <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> 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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    works perfectly. Thanks.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    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,

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    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

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL WHERE clause being ignored? (Access XP)

    Thanks Pat. These are text fields so I had to enclose them in Chr(34) but it works just fine. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •