Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Unanswered: Search Not Finding Matching Records in Table

    Hello All, I have created a search screen, and it has worked well for another DB, so I decided to import those forms to my new DB and make the changes to use it. However, It does not seem to be finding ALL my records in the table. Here is my code for the search screen itself:

    Private Sub Search_Click()
    On Error GoTo Err_Search_Click

    Dim strSql As String
    Dim strWhere As String
    Me.Search_Results.Visible = True


    strSql = "Select * from MARRDIVMAIN where "

    If IsNull(Me.SCounty) = False Then
    strWhere = "COUNTY = " & "'" & Me.SCounty & "'"
    End If

    If IsNull(Me.SEvent) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "EVENT_TYPE = " & "'" & Me.SEvent & "'"
    End If

    If IsNull(Me.Smailed) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "DATE_MAILED = " & "#" & Me.Smailed & "#"
    End If

    If IsNull(Me.SRECD) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "DATE_RECD = " & "#" & Me.SRECD & "#"
    End If



    If strWhere <> "" Then
    strSql = strSql & strWhere

    Me.Search_Results.Form.RecordSource = strSql
    Else:
    Me.Search_Results.Visible = False
    MsgBox "You Must Enter Some Search Criteria.", vbOKOnly, "Invalid Search"

    End If
    If Search_Results.Form.RecordsetClone.RecordCount = 0 Then
    Me.Search_Results.Visible = True
    MsgBox "No Records Found.", vbOKOnly, "No Records Found"
    End If


    Exit_Search_Click:
    Exit Sub

    Err_Search_Click:
    MsgBox Err.Description
    Resume Exit_Search_Click

    And here is what I have on the Search Results Form Code:

    Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "SELECT * FROM MARRDIVMAIN where COUNTY = 'XXXXX'"



    Thanks in advance for your help! - Chris

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    stick
    Code:
    debug.print strSQL
    Or
    Code:
    MsgBox strSQL
    After the full SQL statement is built - this will help you identify where the error is in your SQL statement.
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    Select * from MARRDIVMAIN where

    This appears to be correct.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by cmays637
    Select * from MARRDIVMAIN where

    This appears to be correct.
    No... it doesn't

    WHERE what?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good old fashoned debugging wins again!
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by cmays637
    Select * from MARRDIVMAIN where
    This appears to be correct.
    That's not valid SQL.

    What criteria are you using and which records are not returned? Does it return anything at all?

  7. #7
    Join Date
    Oct 2006
    Posts
    110
    I am selecting a event type from my combo list, choosing "divorce" as a example. Clicking search, I am getting no results although I have several with this event type in my table. After I addied the msgbox, I am getting "Select * from MARRDIVMAIN where" then I click ok, and no results show in my search results form.

    If IsNull(Me.SEvent) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "EVENT_TYPE = " & "'" & Me.SEvent & "'"
    MsgBox strSql
    End If

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Apparently, all your conditions CAN evaluate to false, and in that case strWhere does not get populated, and STRSQL ends up in with a dangling WHERE on the end. You need to remove it if strWhere evaluates to "", or just start with
    strSql = "Select * from MARRDIVMAIN where 1=1"
    Inspiration Through Fermentation

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by cmays637
    If IsNull(Me.SEvent) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "EVENT_TYPE = " & "'" & Me.SEvent & "'"
    MsgBox strSql
    End If
    Your strWhere hasn't been added to strSql at that point.
    MsgBox strWhere would be more interesting there.

    Also, the colon ( : ) after Else seems out of place. Is it in your actual code as well?
    Last edited by ivon; 04-12-07 at 12:12.

Posting Permissions

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