Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL in VB6 (VB6)

    Hello all

    I know how to define a query to search for something in a database...piece of cake, but now, how do I get it to return all the records WITHOUT a certain search string.

    Here is some example code:

    strQuery = "[Description] like '*" & '---c' & "*'"

    'find the first one
    Form1.dtScroll.Recordset.FindFirst strQuery

    Do Until Form1.dtScroll.Recordset.NoMatch
    i = i + 1
    If i > 9 Then
    strSetSpace = ") "
    Else
    strSetSpace = ") "
    End If
    txtrecords = txtrecords & i & strSetSpace & Form1.dtScroll.Recordset.Fields(0).Value
    Form1.dtScroll.Recordset.FindNext query
    Loop

    This writes all records that contain "---c" to a variable which later gets written to a textfile. But now I want to be able to do the same thing but write all records that DO NOT contain "---c". How do I structure the query??

    Thanks!!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VB6 (VB6)

    What about an SQL like

    Select * from myTable where id not in (select id from myTable where description like '*abc*'
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL in VB6 (VB6)

    If it's a Jet database you are querying just add a "NOT" to the Like expression. Example, instead of:
    <pre>"[Description] like '*" & '---c' & "*'"
    </pre>

    use
    <pre>"[Description] Not Like '*---c*'"</pre>

    If the text you are searching/not searching for with Like is not variable then you just need single quotes around the text & wild card characters.

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL in VB6 (VB6)

    thanks, I was trying to use 'notlike; instead of ' not like'

    Oops!!

Posting Permissions

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