Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need a little code help (access 2003 )

    I use the code below on a search form to filter my table for data. The code works pretty well but it has one bug. The text box on my form that captures when the event occurred has 3 types Pre-Admin, Admin and Post-Admin. The problem is whe the user selects one of these types from the dropdown on the search form all the types show because the string below is using the Like statement and * to filter. So eveything shows. Is there a way to modify the lines below to only show exactly what is selected on the search form? So if the user selects Pre-Admin only Pre-admin records will show. Thanks..

    If Not IsNull(Me.txtOccurring) Then
    strWhere = strWhere & " (SiteIssues_tbl.Occurring) Like '*" & Me.txtOccurring & "*' AND"
    End If

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Need a little code help (access 2003 )

    If Not IsNull(Me.txtOccurring) Then
    strWhere = strWhere & " (SiteIssues_tbl.Occurring) =" & Me.txtOccurring & " AND"
    End If

    This assumes that the field Occurring actually holds values such as Pre-Admin, Admin and Post-Admin.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a little code help (access 2003 )

    I tried using this and it shows no results when I try and run it. Is there something wrong? The only three types you can select are Pre-Admin, Admin and Post Admin. Any suggetions? Thanks..

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a little code help (access 2003 )

    Try

    strWhere = strWhere & " (SiteIssues_tbl.Occurring) =" & Chr(34) & Me.txtOccurring & Chr(34) & " AND"

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a little code help (access 2003 )

    I tried this Hans but no luck. Doesn't work. Can you look at the attached sample db and get back to me? Thanks..
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a little code help (access 2003 )

    Problem 1: your SQL selects records from SitesIssues_qry, so you must use SitesIssues_qry.Occurring instead of SiteIssues_tbl.Occurring

    Problem 2: you remove one character too many from the end of strWhere, this makes the final " disappear. You must use

    strWhere = Mid(strWhere, 1, Len(strWhere) - 4)

    instead of

    strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

    Problem 3: there is no space in front of the ORDER BY clause. Use

    Me.lstSearchResult.RowSource = strsql & " " & strWhere & " " & strOrder

    instead of

    Me.lstSearchResult.RowSource = strsql & " " & strWhere & "" & strOrder

Posting Permissions

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