Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    Question Unanswered: one field form based search with list on sub-form; help!!

    So I have Form01 with subform Form02.

    I have TextBox01, and on AfterUpdate, it commands a requery for FormB.

    I have Query01, with 3 fields, Field01, Field02, Field03; then I have another field which is MadeField01: [Field01] & " " & [Field02] & " " & [Field03];

    Then, Query02 presents the results of Query01, and in the criteria for MadeField01, I have the following:

    Like "*" & [Forms]![Form01]![TextBox01] & "*" or [Forms]![Form01]![TextBox01] is null


    HOWEVER...

    I need the search to deliminate(?) TextBox01 and search each term presented in TextBox01.

    E.g.,

    TextBox01.Value: Test Searching Name

    and I want the query to not search "Test Searching Name" but to search "Test" then "Searching" then "Name" and return results for any records with either term matching.


    Please, help!!

    Mark

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Split the contents of the textbox into its constitutive words, then compose the string criteria:
    Code:
        Dim var As Variant
        Dim i As Integer
        Dim strCriteria As String
        
        If Len(Nz(Me.TextBox01.Value, "")) > 0 Then
            var = Split(Me.TextBox01.Value)
            For i = 0 To UBound(var)
                If Trim(var(i)) > 0 Then strCriteria = "[ColumnName] Like '*" & Trim(var(i)) & "*' OR "
            Next i
            strCriteria = strCriteria & "[ColumnName] Is Null"
        End If
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    11
    I'm not really sure what to do with this? Where do I put this?


    Quote Originally Posted by Sinndho View Post
    Split the contents of the textbox into its constitutive words, then compose the string criteria:
    Code:
        Dim var As Variant
        Dim i As Integer
        Dim strCriteria As String
        
        If Len(Nz(Me.TextBox01.Value, "")) > 0 Then
            var = Split(Me.TextBox01.Value)
            For i = 0 To UBound(var)
                If Trim(var(i)) > 0 Then strCriteria = "[ColumnName] Like '*" & Trim(var(i)) & "*' OR "
            Next i
            strCriteria = strCriteria & "[ColumnName] Is Null"
        End If

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This becomes the WHERE part for Query02.
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    11
    I have it like this:

    SELECT MGQ01.RDC, MGQ01.[RD Name], MGQ01.RGC, MGQ01.GRG123, MGQ01.RGA, MGQ01.GRA2_3, MGQ01.RPT, MGQ01.[R Specialty]
    FROM MGQ01
    WHERE
    Dim var As Variant
    Dim i As Integer
    Dim strCriteria As String

    If Len(Nz((Forms!MG01.Form!MK01, "")) > 0 Then
    var = Split(Forms!MG01.Form!MK01)
    For i = 0 To UBound(var)
    If Trim(var(i)) > 0 Then strCriteria = "[RD Name] Like '*" & Trim(var(i)) & "*' OR "
    Next i
    strCriteria = strCriteria & "[RD Name] Is Null"
    End If


    However, I'm getting the following error:

    Syntax error (missing operator) in query expression 'Dim var As variant...


    Please, help!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You have to dynamically modify the SQL statement of the second query after computing the value of strCriteria:
    Code:
    Dim dbs as DAO.Database
    Dim qdf as DAO.Querydef
    Set dbs = Currentdb
    Set qdf = dbs.QueryDefs("Query02")
    qdf.SQL = "SELECT MGQ01.RDC, MGQ01.[RD Name], MGQ01.RGC, MGQ01.GRG123,  GQ01.RGA, MGQ01.GRA2_3, MGQ01.RPT, MGQ01.[R Specialty] FROM MGQ01 WHERE " & strCriteria
    qdf.close
    Currentdb.QueryDefs.Refresh
    Set qdf = Nothing
    Set dbs = Nothing
    Have a nice day!

  7. #7
    Join Date
    Apr 2011
    Posts
    11
    I'm not sure where I put this code or the previous code?

    Do I put it in the SQL of the query, or make module, or?

    I'm not sure what it means to dynamically modify the SQL statement

  8. #8
    Join Date
    Apr 2011
    Posts
    11
    I really appreciate your help.... I'm sorry, I'm just faimiliar with Mircosoft Access (very thoroughly) but I'm not so knowledgeable with SQl.... I'm working it from Visual Basics...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Grouping everything together:
    Code:
    Private Sub TextBox01_AfterUpdate()
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim var As Variant
        Dim i As Integer
        Dim strCriteria As String
        
        If Len(Nz(Me.TextBox01.Value, "")) > 0 Then
            var = Split(Me.TextBox01.Value)
            For i = 0 To UBound(var)
                If Trim(var(i)) > 0 Then strCriteria = "[ColumnName] Like '*" & Trim(var(i)) & "*' OR "
            Next i
            strCriteria = strCriteria & "[ColumnName] Is Null"
            Set dbs = CurrentDb
            Set qdf = dbs.QueryDefs("Query02")
            qdf.SQL = "SELECT MGQ01.RDC, MGQ01.[RD Name], MGQ01.RGC, MGQ01.GRG123, GQ01.RGA, MGQ01.GRA2_3, MGQ01.RPT, MGQ01.[R Specialty] " & _
                      "FROM MGQ01 WHERE " & strCriteria
            qdf.Close
            CurrentDb.QueryDefs.Refresh
            Set qdf = Nothing
            Set dbs = Nothing
            '
            ' If Query02 is the RecordSource of a form (say "Form02") you can actualize it now:
            '
            ' Forms("Form02").Requery
            '
        End If
        
    End Sub
    Have a nice day!

  10. #10
    Join Date
    Apr 2011
    Posts
    11
    Wow, that's really awesome, and it works very well. Do you do this professionally?

    How do I add multiple columns to this? How Do I add ColumnName02, and ColumnName03?

    Thank you!

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by MFleysher View Post
    Wow, that's really awesome, and it works very well. Do you do this professionally?
    Not here! But yes, I'm an application developer since 1985 and I use Access since its version 1.1.

    Quote Originally Posted by MFleysher View Post
    How do I add multiple columns to this? How Do I add ColumnName02, and ColumnName03?
    With an AND linking operator (ColumnName01 Like... ) AND (ColumnName02 Like...) or with an OR operator (ColumnName01 Like... OR ColumnName02 Like...)? Also using TextBox01 for Col_02 and Col_03 or using TextBox02 and TextBox03? Please explain more precisely.

    Quote Originally Posted by MFleysher View Post
    Thank you!
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Apr 2011
    Posts
    11
    SinnDHo,

    I'll try it out within a few hours...

    Do you know mysql and php? or something better?

    I'm developing 2 systems in access and I want to take it out of access... let me know if this a kind of project that interests you. Mark 702-291-8186

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Mark,

    Thank you for your offer. However I work for a multinational company and its policy strongly discourage members of its staff to work on the side. Moreover, I'm not sure I would have enough free time to do it anyway.

    This does not prevent me from trying to help, though.

    I'm more used to MS SQL Server and Oracle than to MySQL. I don't usually use php, the development tools I work with involve VB/VBA (Access and Excel), .Net (C# and VB.Net) and C++.
    Have a nice day!

  14. #14
    Join Date
    Apr 2011
    Posts
    11
    Sinn, thanks for all your help thus far!

    So, here's where I'm at:

    Private Sub MK01_AfterUpdate()


    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim var As Variant
    Dim i As Integer
    Dim strCriteria As String


    If Forms!MG01!AnyAll = 2 Then
    If Len(Nz(Me.MK01.Value, "")) > 0 Then
    var = Split(Me.MK01.Value)
    For i = 0 To UBound(var)
    If Trim(var(i)) > 0 Then strCriteria = "([All]) Like '*" & Trim(var(i)) & "*' Or "
    Next i
    strCriteria = strCriteria & "Forms!MG01!MK01 Is Null"
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Form_MG02_03")
    qdf.SQL = "SELECT Form_MG02_02.RGC, Form_MG02_02.[All] " & _
    "FROM Form_MG02_02 WHERE " & strCriteria
    qdf.Close
    CurrentDb.QueryDefs.Refresh
    Set qdf = Nothing
    Set dbs = Nothing

    Forms![MG01]![MG02].Requery
    '
    '
    Else

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Form_MG02_03")
    qdf.SQL = "SELECT Form_MG02_02.RGC, Form_MG02_02.[All] " & _
    "FROM Form_MG02_02"
    qdf.Close
    CurrentDb.QueryDefs.Refresh
    Set qdf = Nothing
    Set dbs = Nothing

    Forms![MG01]![MG02].Requery
    End If
    Else
    If Forms!MG01!AnyAll = 1 Then
    If Len(Nz(Me.MK01.Value, "")) > 0 Then
    var = Split(Me.MK01.Value)
    For i = 0 To UBound(var)
    If Trim(var(i)) > 0 Then strCriteria = "([All]) Like '*" & Trim(var(i)) & "*' Or "
    Next i
    strCriteria = strCriteria & "Forms!MG01!MK01 Is Null"
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Form_MG02_03")
    qdf.SQL = "SELECT Form_MG02_02.RGC, Form_MG02_02.[All] " & _
    "FROM Form_MG02_02 WHERE " & strCriteria
    qdf.Close
    CurrentDb.QueryDefs.Refresh
    Set qdf = Nothing
    Set dbs = Nothing

    Forms![MG01]![MG02].Requery
    '
    '
    Else

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("Form_MG02_03")
    qdf.SQL = "SELECT Form_MG02_02.RGC, Form_MG02_02.[All] " & _
    "FROM Form_MG02_02"
    qdf.Close
    CurrentDb.QueryDefs.Refresh
    Set qdf = Nothing
    Set dbs = Nothing

    Forms![MG01]![MG02].Requery
    End If
    End If
    End If


    End Sub





    Now... If Forms!MG01!AnyAll = 1 Then

    I would like the query results to show results based the forms textbox but not or I want and, and I can't figure it out...

    So if I search for Attorney 89146, I want only the records that have both "Attorney" and ""89146" anywhere in the column, in any order...

    So... I can search "Attorney 89146" or "89146 Attorney" and I'd like my results to show records that include both words....

    The way it does it now is it will return any records that have 89146 OR Attorney, so including the 89146 is useless because it shows all attorneys anyway... I would like my search to narrow with more terms, not expand...

    Can you help with this?

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I correctly understand the question, you want to search for any word in MK01 matching a word in [All] when AnyAll = 2, search for every word in MK01 matching a word in [All] when AnyAll = 1.

    If so, this should work:
    Code:
    Private Sub MK01_AfterUpdate()
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim var As Variant
        Dim i As Integer
        Dim strSQL As String
        Dim strCriteria As String
        Dim strOperator As String
        
        strSQL = "SELECT Form_MG02_02.RGC, Form_MG02_02.[All] FROM Form_MG02_02"
        If Len(Nz(Me.MK01.Value, "")) > 0 Then
            strOperator = Nz(Choose(Forms!MG01!AnyAll.Value, " And ", " Or "), "")
            If Len(strOperator) = 0 Then
                ' Handle error: Forms!MG01!AnyAll.Value is neither 1 nor 2
            End If
            var = Split(Me.MK01.Value)
            For i = 0 To UBound(var)
                If Len(strCriteria) > 0 Then strCriteria = strCriteria & strOperator
                If Trim(var(i)) > 0 Then strCriteria = "( [All] Like '*" & Trim(var(i)) & "*' )"
            Next i
            strCriteria = strCriteria & " Or Forms!MG01!MK01 Is Null"
            strSQL = strSQL & " WHERE " & strCriteria
        End If
        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("Form_MG02_03")
        qdf.SQL = strSQL
        qdf.Close
        CurrentDb.QueryDefs.Refresh
        Set qdf = Nothing
        Set dbs = Nothing
        Forms![MG01]![MG02].Requery
        
    End Sub
    Notice that there is no apparent test on the value of AnyAll. The distinction is made using the Choose() function that select the appropriate logical operator.
    Have a nice day!

Tags for this Thread

Posting Permissions

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