Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: SQL : * versus %

    In my DB (Access 2003) I have a listbox containing 200 names of employees.
    To make browsing easier I created checkboxes (one for each departement). When the user checks a checkbox, only the people who belong to the selected departement are shown.

    So I wrote a function to create a SQL-statement :

    Code:
    Public Function getSQL (chk1, chk2, chk3) As String
                
        getSQL  =  "SELECT * FROM myTable WHERE "
                                     
            If chk1 = True 
               Then getSQL = getSQL  &  "ao3_omschr LIKE 'DEPT1*' OR "
            End If
            
            If chk2 = True 
               Then getSQL = getSQL  &  "ao3_omschr LIKE 'DEPT2*' OR "
            End If
    
            If chk3 = True 
               Then getSQL = getSQL  &  "ao3_omschr LIKE 'DEPT3*' OR "
            End If
    
            If Right (getSQL, 3) = "OR " Then
                getSQL = Left (getSQL, Len(getSQL) - 3) & "ORDER BY per_compacte_nm"
            Else
                getSQL = Left (getSQL, Len(getSQL) - 6) & "ORDER BY per_compacte_nm"
            End If
            
    End Function
    As you can see I use wildcards ('*') in my statement.
    This works fine until .... they call me because it doesn't work anymore.
    When I change all the '*' by '%' everything is OK again until .... they call me because it doesn't work anymore.
    When I change all the '%' by '*' everything is OK again until .... they call me because it doesn't work anymore.
    And so on and so on.

    Why is one time the '*' the right wildcard and an other moment I have to use the '%' ??

    Anyone any idea?

    Thanks
    Last edited by artemide; 10-27-03 at 10:42.

  2. #2
    Join Date
    Oct 2003
    Posts
    311

    Re: SQL : * versus %

    I believe the * is used in the access SQL but when using VBA Use %, so if your sql to fill the cbo is created with the qry enviroment thing use * but if your using dao or ado use % hope it helps but i know i have found this to be true in my dealings


    M~

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In Access "*" is used within the QBE for wildcard matching. In VBA code the "%" is used ... Now if you're trying to connect to other DB types (Oracle, SQL Server, etc ...) The wildcard(s) may be different and you'll have to experiment to get the right one(s).

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by M Owen
    In Access "*" is used within the QBE for wildcard matching. In VBA code the "%" is used ... Now if you're trying to connect to other DB types (Oracle, SQL Server, etc ...) The wildcard(s) may be different and you'll have to experiment to get the right one(s).
    In the beginning my listbox is populated by using an Access-query.
    As soon a checkbox is checked I use the sql-statement I created using VB as the rowsource of my listbox.
    I only use Access-databases.
    And the strange thing is that one moment it works and an other moment it doesn't.
    And I didn't change a thing on my database.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Art,

    When you say "In the beginning my listbox is populated by using an Access-query" are you saying that a design time you set the rowsouce to be a query you created in the QBE? And then afterwards you update the rowsource as needs/circumstances change? I see your query construction ... How about posting your assignment code to the listbox ...

    - Mike

  6. #6
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by M Owen
    Art,

    When you say "In the beginning my listbox is populated by using an Access-query" are you saying that a design time you set the rowsouce to be a query you created in the QBE? And then afterwards you update the rowsource as needs/circumstances change? I see your query construction ... How about posting your assignment code to the listbox ...

    - Mike

    This is how I did it:
    In designview I set rowsourceType of myListBox to Table/Query, but I left rowSource empty.
    Then I run this code :

    Code:
    Private Sub Form_Load()
        call UpdateListBox
    End Sub
    
    ****************
    
    Private Sub UpdateListBox()
        myListBox.RowSource = getSQL (chk1.value, chk2.value, chk3.value)
        myListBox.Requery
    End Sub
    
    ****************
    
    Public Function getSQL (chk1, chk2, chk3) As String
                
        getSQL  =  "SELECT * FROM myTable WHERE "
                                     
            If chk1 = True 
               Then getSQL = getSQL  &  "ao3_omschr LIKE 'DEPT1*' OR "
            End If
            
            If chk2 = True 
               Then getSQL = getSQL  &  "ao3_omschr LIKE 'DEPT2*' OR "
            End If
    
            If chk3 = True 
               Then getSQL = getSQL  &  "ao3_omschr LIKE 'DEPT3*' OR "
            End If
    
            If Right (getSQL, 3) = "OR " Then
                getSQL = Left (getSQL, Len(getSQL) - 3) & "ORDER BY per_compacte_nm"
            Else
                getSQL = Left (getSQL, Len(getSQL) - 6) & "ORDER BY per_compacte_nm"
            End If
            
    End Function

Posting Permissions

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