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

    Question Unanswered: Help limiting Search Results to active records

    Hello all.. I have a search screen form where I am trying to limit my results to only active records. I have a checkbox field in the table called active.

    I am not sure where to put the SQL code to show only active records. I thought it would of gone where I have:

    strSQL = "select * from hospital_users where hospital_users.active='true';"

    but it doesnt run correctly.


    I have done searches on this topic, but havent found anything that is working for me.
    Here is my code. Thanks in advance for your help!


    Dim strSQL As String
    Dim strWhere As String
    Me.HD_Search_Results.Visible = True
    Me.S_Count.Visible = True
    Me.S_Count_Label.Visible = True
    Me.S_label_clickedit.Visible = True


    Dim AstrSQL As String

    AstrSQL = "INSERT INTO Audit(UserID,Log_Date,task)" & "Values(" & "'" & usercode() & "'" & "," & "'" & Now() & "', '" & Task & "'" & ")"

    CurrentDb.Execute AstrSQL

    strSQL = "select * from hospital_users where "


    If IsNull(Me.Suser) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "Users = " & "'" & Me.Suser & "'"
    End If
    If IsNull(Me.SFacility) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "Hospital = " & "'" & Me.SFacility & "'"
    End If
    If IsNull(Me.s_county) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "county = " & "'" & Me.s_county & "'"
    End If
    If IsNull(Me.s_city) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "City = " & "'" & Me.s_city & "'"
    End If
    If IsNull(Me.s_facility_code) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "facility_code = " & "'" & Me.s_facility_code & "'"
    End If
    If IsNull(Me.S_TicketID) = False Then
    strWhere = "hospID = " & Me.S_TicketID
    End If

    If strWhere <> "" Then
    strSQL = strSQL & strWhere & " ORDER BY active asc, hospital asc, users asc "
    Me.HD_Search_Results.Form.RecordSource = strSQL
    Me!S_Count.Value = HD_Search_Results.Form.RecordsetClone.RecordCount
    Else:
    Me.HD_Search_Results.Visible = False
    Me.S_Count.Visible = False
    Me.S_Count_Label.Visible = False
    MsgBox "You Must Enter Some Search Criteria.", vbOKOnly, "Hospital Users Search"
    End If
    If HD_Search_Results.Form.RecordsetClone.RecordCount = 0 Then
    Me.HD_Search_Results.Visible = False
    Me.S_label_clickedit.Visible = False
    Me.S_Count.Value = "0"
    MsgBox "No User(s) Found.", vbOKOnly, "Hospital Users Search"
    End If

    Exit_Search_Click:
    Exit Sub

    Err_Search_Click:
    MsgBox Err.Description
    Resume Exit_Search_Click

    End Sub
    Last edited by cmays637; 02-18-10 at 14:44.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The problem I see right away is 'true' is a literal string. If you want to compare to a boolean value (True or False) then remove the quotes. Otherwaise you are comparing a boolean with a string and it will not be true.

    Code:
    strSQL = "select * from hospital_users where hospital_users.active=true;"

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    Thanks for your help, but trying that I am getting error msg: Characters found after end of SQL statement.?

  4. #4
    Join Date
    Oct 2009
    Posts
    340

    Exclamation

    general advice; build your queries in the query object area using the design grid.....it is one of the best features of Access....

    it is easy to test and vary the query....

    then you can simply call the query by name....or if you prefer - change the design view to sqlview - and copy/paste the syntax into a vb statement....

  5. #5
    Join Date
    Oct 2006
    Posts
    110
    I did try that, and it works in the query, but running it from the from gives me the error.

    My subform that provides the results from the search form works too. But running it from my search form does not.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try removing the semi colon at the end of the string, although SQL should be terminated with semi colons I've a nasty suspisicion that JET doesn't like 'em
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2006
    Posts
    110
    I couldn't get past the SQL error, so I decided to go with a search field on my form that is defaulted to true.

    I could hide the field and it would serve the same purpose.
    Thanks for your help.

Posting Permissions

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