Results 1 to 13 of 13
  1. #1
    simba is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Posts
    34

    search

    I have a requirement of searching the database based on 3 columns. I have attached the search form and the code. Not sure, how to fine tune this. Can someone help?

    Private Sub cmdSearch_Click()
    Dim strSQLHead As String
    Dim strSQLWhere As String
    Dim strSQLOrderBy As String
    Dim strSQL As String
    Dim strJoin As String

    strJoin = " AND "
    strSQLHead = "SELECT * FROM QryResourceContacts "

    If Len(Me.txtZipCode & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [Zipcode] Like " & Chr$(39) & "*" & Me.txtZipCode & "*" & Chr$(39)


    Else
    strSQLWhere = "WHERE [Zipcode] = " & Chr$(39) & Me.txtZipCode & Chr$(39)
    End If

    strSQLWhere = strSQLWhere & strJoin

    End If

    If Len(Me.cboCountyName & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [CountyName] Like " & Chr$(39) & "*" & Me.cboCountyName & "*" & Chr$(39)
    Else
    strSQLWhere = "WHERE [CountyName] = " & Chr$(39) & Me.cboCountyName & Chr$(39)
    End If

    strSQLWhere = strSQLWhere & strJoin

    End If

    If Len(Me.cboTypeOfService & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [TypeOfService] Like " & Chr$(39) & "*" & Me.cboTypeOfService & "*" & Chr$(39)
    Else
    strSQLWhere = "WHERE [TypeOfService] = " & Chr$(39) & Me.cboTypeOfService & Chr$(39)
    End If

    strSQLWhere = strSQLWhere & strJoin

    End If


    If Len(strSQLWhere) Then
    strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
    End If


    strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

    Me.LstRecords.RowSource = strSQL

    End Sub

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Have you tried putting a break point
    [left-click in the gray margin to the left of the code window]
    in your code on this line:

    Code:
     
    Me.LstRecords.RowSource = strSQL
    and then hovering your mouse over the strSQL?

    OR - put:

    Code:
     
    MsgBox strSQL
    just before that line of code.

    That way you get to see the SQL statement that you are generating in your code and you will get a better idea if it is exactly what you need.

    What problem are you having with your SQL?

  3. #3
    simba is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Posts
    34
    Thanks Robin for your reply. Yes, I do check using breakpoint.

    The keyed in zipcode and the other two parameters are captured in the breakpoint correctly.

    But my problem is, it is not filtering the list based on the search terms.

    Need assistance, with that.

    Thanks once again, in advance.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Does your constructed SQL 'Statement' have correct SQL syntax?

    Can you post the exact SQL statement that is coming out of your code? I'm wondering if that might be the problem.

  5. #5
    simba is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Posts
    34
    Robeen! This is the code that I have. Do not know to interpret it.

    Private Sub cmdSearch_Click()
    Dim strSQLHead As String
    Dim strSQLWhere As String
    Dim strSQLOrderBy As String
    Dim strSQL As String
    Dim strJoin As String

    strJoin = " AND "
    strSQLHead = "SELECT * FROM QryResourceContactsColumn "

    If Len(Me.txtZipCode & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [Zipcode] Like " & Chr$(39) & "*" & Me.txtZipCode & "*" & Chr$(39)
    Else
    strSQLWhere = "WHERE [Zipcode] = " & Chr$(39) & Me.txtZipCode & Chr$(39)
    End If

    strSQLWhere = strSQLWhere & strJoin

    End If

    If Len(Me.txtCountyName & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [CountyName] Like " & Chr$(39) & "*" & Me.txtCountyName & "*" & Chr$(39)
    Else
    strSQLWhere = "WHERE [CountyName] = " & Chr$(39) & Me.txtCountyName & Chr$(39)
    End If

    strSQLWhere = strSQLWhere & strJoin

    End If

    If Len(Me.txtTypeOfService & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [TypeOfService] Like " & Chr$(39) & "*" & Me.txtTypeOfService & "*" & Chr$(39)
    Else
    strSQLWhere = "WHERE [TypeOfService] = " & Chr$(39) & Me.txtTypeOfService & Chr$(39)
    End If

    strSQLWhere = strSQLWhere & strJoin

    End If

    If Len(strSQLWhere) Then
    strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
    End If

    strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

    Me.LstRecords.RowSource = strSQL

    End Sub

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Try this:

    Code:
     
    Debug.Print strSQL
    before your End Sub.

    Copy out the SQL statement that shows in the debug window & post it here.

    That way we'll be able to see if the SQL statement you are creating has correct syntax.

    Or - you can put

    Code:
     
    MsgBox strSQL
    in the same spot in your code - and copy out the SQL statement that your MsgBox displays - and post it here.

  7. #7
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I found this page [debugging tips & tricks] helpful too.
    http://support.microsoft.com/kb/142999


  8. #8
    simba is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Posts
    34
    This is the error message, I am getting even when I had entered the three values.

    It is capturing only the last entered field. When I enter zipcode and county, Where clause is using only County.

    When I use zipcod, county and type of service, where clause is using only type of service for the search term.

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You should be getting:

    . . . "Child Development/Therapy Services/Screening programs"

    You are getting single quotes around that when it needs to be double quotes.

    check this page:
    http://www.baldyweb.com/wherecondition.htm

    for a really good example of how to pass double quotes.

    Here's Paul's example - you can use the technique in how you build your SQL statement:

    Code:
     
    DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'" '(note, that's a double quote, single quote and double quote at the end)
    I hope that fixes your problem.

  10. #10
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You are over-writing what is in your strSQLWhere variable.

    At the end of each of your IF clauses:

    If Len(Me.txtZipCode & vbNullString) Then . . .
    If Len(Me.txtCountyName & vbNullString) Then . . .
    If Len(Me.txtTypeOfService & vbNullString) Then . . .

    You are overwriting the strSQLWhere variable with this:

    Code:
    strSQLWhere = strSQLWhere & strJoin
    so your Where clause will only contain the last one that has been written.

    You have to add the new clause to the existing one with an " AND " instead of overwriting.

    I hope that helps. Let us know if you still have trouble.

  11. #11
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    simba,

    This is NOT working code but it is meant to give you an idea of what I think you need to do to get all the clauses into your strSQLWhere variable.

    You might have to tweak it a little.

    PLUS - I think you are not getting the correct " [double-quotes] around your variables. If I have more time, I will work on it.

    Hopefully this will give you a starting point.

    Code:
     
    If Len(Me.txtZipCode & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = "WHERE [Zipcode] Like " & Chr$(39) & "*" & Me.txtZipCode & "*" & Chr$(39)
    Else
    strSQLWhere = "WHERE [Zipcode] = " & Chr$(39) & Me.txtZipCode & Chr$(39)
    End If
    End If
     
    If Len(Me.txtCountyName & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = strSQLWhere & " AND " & [CountyName] Like " & Chr$(39) & "*" & Me.txtCountyName & "*" & Chr$(39)
    Else
    strSQLWhere = strSQLWhere & " AND " & [CountyName] = " & Chr$(39) & Me.txtCountyName & Chr$(39)
    End If
    End If
     
    If Len(Me.txtTypeOfService & vbNullString) Then
    If (Me.chkLike) Then
    strSQLWhere = strSQLWhere & " AND " & [TypeOfService] Like " & Chr$(39) & "*" & Me.txtTypeOfService & "*" & Chr$(39)
    Else
    strSQLWhere = strSQLWhere & " AND " & [TypeOfService] = " & Chr$(39) & Me.txtTypeOfService & Chr$(39)
    End If
    End If
     
    strSQLWhere = strSQLWhere & strJoin 'Put this after you've constructed your Where clause
    Once again - I have not had time to test this but I'm putting it here so you have something to work with.

    Let me know if you still need help & when I get time, I'll try getting it to actually work in Access.

    All the best!

  12. #12
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi,

    I got a little time and so I ran this quick mock-up to create a SQL string that is syntactically correct.

    I have hard-coded values into variables because I didn't have time to create the Form that you have and use the Values from controls on the Form.

    Still - the SQL comes out correct and I hope you can use this example to solve your problem.

    Code:
     
    Function CreateSQLSearchString()
    Dim strSQLHead As String
    Dim strSQLWhere As String
    Dim strSQLOrderBy As String
    Dim strSQL As String
    Dim strJoin As String
     
    '********** My Test Code
    Dim strZip, strCountry, strService As String
    Dim BoolLike As Boolean
    strZip = "12345"
    strCountry = "Tanzania"
    strService = "Child Development programs"
    BoolLike = False
     
    '********** End - My Test Code
     
    strJoin = " AND "
    strSQLHead = "SELECT * FROM QryResourceContactsColumn "
     
    '********** My Test Code
    If Len(strZip & vbNullString) Then
    If (BoolLike) Then
    strSQLWhere = "WHERE Zipcode Like '" & Chr$(39) & "*" & strZip & "*" & Chr$(34)
    Else
    strSQLWhere = "WHERE Zipcode = " & Chr$(34) & strZip & Chr$(34)
    End If
    End If
     
    If Len(strCountry & vbNullString) Then
    If (BoolLike) Then
    strSQLWhere = strSQLWhere & " AND CountyName Like " & Chr$(34) & "*" & strCountry & "*" & Chr$(34)
    Else
    strSQLWhere = strSQLWhere & " AND CountyName = " & Chr$(34) & strCountry & Chr$(34)
    End If
    End If
    If Len(strService & vbNullString) Then
    If (BoolLike) Then
    strSQLWhere = strSQLWhere & " AND TypeOfService Like " & Chr$(34) & "*" & strService & "*" & Chr$(34)
    Else
    strSQLWhere = strSQLWhere & " AND TypeOfService = " & Chr$(34) & strService & Chr$(34)
    End If
    End If
     
    '********** End - My Test Code
     
    strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
     
    MsgBox strSQL            
     
    End Function
    I have attached jpg's of the two sql strings in my MsgBox.

    I hope this helps.

  13. #13
    simba is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Posts
    34
    Thanks Robeen for your immense help. Unfortunately, I cannot test out the code until next Wednesday, as I am outside of work.

    Will let you know the results, whatever that may be.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-17-2011, 06:17 AM
  2. Search Log!!!!!!!!!?!?!?!?!?
    By JG1992 in forum Queries
    Replies: 2
    Last Post: 03-13-2011, 03:09 PM
  3. help in search
    By Nokia N93 in forum Programming
    Replies: 5
    Last Post: 01-14-2011, 01:49 PM
  4. Replies: 2
    Last Post: 08-31-2010, 08:57 PM
  5. Search
    By DWS in forum Forms
    Replies: 3
    Last Post: 08-24-2009, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums