Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Unanswered: comparing two memo fields

    Hello,
    I have two distinct tables, from these tables I want to compare two fields containing text values (keywords) whose data type is memo.
    they look like this:
    RecID Value
    1 tree
    1 mountains
    2 orange
    ...
    1003 pluto
    1003 saturn
    1003 mercury
    1003 earth

    but, being a noobie to access programming, I don't know much about SQL programming...
    I prefer for the moment learnig how to use VBA to define small codes, and use these in a query or a macro... That's what I did for all the prevous work I have done on this project.

    So my questions is :
    How could I compare my two tables for occurances of a same keyword in both tables? thx for your answer.
    My goal is to obtain a resulting table containing fields for each matching keywords and its RecIds in the two tables where it is found...

    regards,

    nico

  2. #2
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    this bit of code will give you a string will all the id's of matching values. thel ist will be delimited with ;. I thought this would make it easier for importing into a table or something.

    I haven't tested it so I hope it helps.

    Dim Db As Database
    Dim Rs1 As Recordset
    Dim Rs2 As Recordset
    Dim StrIds As String

    Set Db = CurrentDb()


    Set Rs1 = Db.opensrecordset("table1")
    Rs1.MoveFirst
    While Not Rs1.EOF
    Set Rs2 = Db.OpenRecordset("select * from [table2] where [value] = " & Rs1![Value])
    If Rs2.EOF And Rs2.BOF Then
    'there are no records for this value
    Else
    'there are matching records
    Rs2.MoveFirst
    While Not Rs2.EOF
    StrIds = StrIds & ";" & Rs2![Value]
    Rs2.MoveNext
    Wend
    End If
    Rs2.Close
    Rs1.MoveNext
    Wend
    Rs1.Close


    'John

  3. #3
    Join Date
    Apr 2003
    Posts
    42

    See the Example

    Hey,

    I have too much time on my hands at the moment.

    Look at the code behind the button on the form:

    frmSearchTables

    Hopefully this will get you started on how to execute SQL queries via VB code.

    The coding is a little messy but does the job.

    IIIVentelationIII
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Smile

    hey thank you for the job! it's cool!
    but the final step of my task will be to automate fully the data work. Concerning your example, I have to click "GO" to start the matching work.... won't that be a problem if I want to use succeding macros to process my task? I'll try to use part of the code associated with the button "GO" and use a sub macro on it to process the matching automatically.
    One more thing, given that my tbl1 has 20000 entries or keywords and that tbl2 will be a lot smaller (less than a couple hundred for the moment), which table in your code is used to give the keyword which is searched for in the other table. Because, logically the smaller table should give the words, should'nt it?
    thx for your time
    nico
    Last edited by nicolascaprais; 07-02-03 at 13:17.

  5. #5
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Question

    Hey!
    found the solution to my preceding question I'll use tbl1 as the samllest table.
    So I transformed the code to this in order to use it with a macro:
    Code:
    Option Compare Database
    Function CompareFields()
    
    
        'declare variables
        Dim dbCurrent As Database
        Dim rstTableOne As Recordset
        Dim rstTableTwo As Recordset
        Dim rstTableThree As Recordset
        
        Dim CompResult As Variant
        
        
        'open database
        Set dbCurrent = CurrentDb()
        
        'set recordset to TableOne
        '   NOTE: the text between the " " here can be any SQL statement
        Set rstTableOne = dbCurrent.OpenRecordset("tblClientKeyword")
            
        'set recordset to TableTwo
        Set rstTableTwo = dbCurrent.OpenRecordset("tblNewKeyword")
        
        'set recordset to TableThree
        Set rstTableThree = dbCurrent.OpenRecordset("tblresults")
        
        
        'goto first record in TableOne
        If Not (rstTableOne.EOF) Then
            rstTableOne.MoveFirst
        Else
            MsgBox ("Table One is Empty!")
        End If
        
        
        
        'quick fix, error check incase TableTwo is empty
        '     If this is not here and TableTwo has no data
        '     then this program crash
        If rstTableTwo.EOF Then
            MsgBox ("Table Two is Empty!")
            Exit Function
        End If
            
        
        
        
        'loop through each record till end of records in TableOne
        Do Until (rstTableOne.EOF)
     
        
            'loop through each record till end of records in TableTwo
            Do Until rstTableTwo.EOF
            
                'compare records in TableOne and TableTwo
                CompResult = StrComp(rstTableOne!Value, rstTableTwo!Value, vbTextCompare)
                 
                'note if string is the same then 0 is returned,
                If CompResult = 0 Then
                    
                    'MsgBox ("String is the same")
                    
                    'copy data to TableThree
                    With rstTableThree
                        .AddNew
                    
                        !Value = rstTableOne!Value
                        !RecId_TableOne = rstTableOne!RecId
                        !RecId_TableTwo = rstTableTwo!RecId
                    
                        .Update
                    End With
                
                
                Else
                    
                    'MsgBox ("String not the same")
                
                End If
                
                'MsgBox ("Table Two Id: " & rstTableTwo!RecId)
                
                
                'move to next record in recordset
                rstTableTwo.MoveNext
                
            Loop
            
            'MsgBox ("Table One Id: " & rstTableOne!RecId)
            
            'goto first record in TableTwo if End Of File
            If (rstTableTwo.EOF) Then
                rstTableTwo.MoveFirst
            End If
            
            'move to next record in TableOne
            rstTableOne.MoveNext
            
        Loop
        
        'close recordsets
        rstTableOne.Close
        rstTableTwo.Close
        rstTableThree.Close
        
        'set to nothing
        Set rstTableTwo = Nothing
        Set rstTableThree = Nothing
        Set rstTableThree = Nothing
        
        MsgBox ("Finished!")
        
    End Function
    
    Sub compare()
    
    End Sub
    And I get this: "error code 3265 execution error"
    on this line
    Code:
    CompResult = StrComp(rstTableOne!Value, rstTableTwo!Value, vbTextCompare)
    Is this a dictionnary problem or something else?
    thx nico
    Last edited by nicolascaprais; 07-02-03 at 13:44.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi nico,

    do you have fields named [Value] in each recordset?

    "!" needs to be followed by a field name.


    izy

  7. #7
    Join Date
    Apr 2003
    Posts
    42

    Lightbulb

    Originally posted by nicolascaprais

    And I get this: "error code 3265 execution error"
    on this line
    Code:
    CompResult = StrComp(rstTableOne!Value, rstTableTwo!Value, vbTextCompare)
    Is this a dictionnary problem or something else?
    thx nico
    Not sure what the error would be...
    If it's a dictionary problem, I would think that it would come up with an error like:

    (Compiler Error: Sub or Function not defined)

    Did this only happen when you copied the code to another DB? ie. It worked fine when running the example but not when used in another DB.

    Suggestion: if my example DB works and yours does not, then it could be worth checking and comparing the References... (library) included in both DBes. In the code window, click on TOOLS, References.


    Try and issolate the problem...

    Display the data being compared in a msgbox so that you can see and test the data that is being compared.

    I wouldn't think so, but it could be a problem with the memo data type.

    Try adding:

    Code:
    Dim strStringA as string
    Dim strStringB as string
    
    strStringA = rstTableOne!Value
    strStringB = rstTableTwo!Value
    
    CompResult = StrComp(strStringA, strStringB, vbTextCompare)
    Dunno, only guessing at what it could be. I can't find a way on my DB to cause the same error you have.

  8. #8
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88
    Hey guyz!
    thanks for your help!! it works fine!!!
    izyrider I see that you have a good perception on my VBA programming skills... I had'nt understood that the field value had to be changed to the real field names of each table... It's all on me... Sorry ventelation to have made you think on that...
    Thx again...
    Now that I can match each of my keywords, I'd like to know if there's a way to define the case sensitiveness or not in the code?
    Because, for example, if I am searching through table 2 for the key word "accessory", the only matching found is for the same word... But there are fields in table 2 containing the keywords "Accessory reproductive glands".

    I'd like to be able to have a match on this search, even though the field from table 2 contains only part of the keyword searched. For this problem I separated the compound keywords "Test of materials" contained in one field into three fields containing "test", "of", "materials". But the inconvenient of this technique is that I am going to have way more matches than before. Therefore, I'll have "to weighten" the matchings sort of like in a google search... Where for a specific multiple keywords search coming from a single entry, a percentage is returned as:
    "from client number 12's 10 keywords, there are:
    - 7 keywords matching with partnership offer number 68 (70%)
    - 1 keywords matching with partnership offer number 68 (10%)
    ... "
    Therefore some matches made on common, regular, non relevant keywords as articles (of, an, a, the.... you know them... ) won't get as much attention as a true match with pertinent keywords...

    My other problem is that I want a match even though keywords are written in a different manner: "Test" and "test" for example should return me a hit. Should I turn any capital letter into a small letter in my keywords field?

    How can I do all of that?
    thx for your time...

    nico
    Last edited by nicolascaprais; 07-03-03 at 07:47.

  9. #9
    Join Date
    Apr 2003
    Posts
    42

    Lightbulb

    Look at the other form in the example DB I attached previously: frmSearchString

  10. #10
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Question

    Then what I need in order to get a matching hit even though part of the word is searched is to use the comparison code from cmdSearch in the cmdDo?

  11. #11
    Join Date
    Apr 2003
    Posts
    42

    Arrow Voila

    I may have misunderstood your other post.

    The other code on the other form will look in StringA for StringB and tell you if there it is found regardless of capitlisation.

    So keyword: "accessory"
    Will be found in: "Accessory reproductive glands"
    Even in: "Reproductive gland ACceSSoRy"

    <See attachment.>
    Attached Files Attached Files

  12. #12
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Thumbs up

    hey thx man!
    tried it works fine for me thanks! I don't think I'll go higher in difficulty and to try to find misstyped words (e.g. pheontype instead of phenotype)!!! Otherwise it'll be hell!!!
    appreciate your help on this one! c u around
    nico
    Last edited by nicolascaprais; 07-07-03 at 10:24.

  13. #13
    Join Date
    Apr 2003
    Posts
    42
    No worries.

    I hope u have learnt a little from my code. I had some time to kill and it was a small challenge.

    I am also pretty new to Access / VB programming. The code most likely can be improved.

    There's heaps of previously written string routines etc in VB that you can usually adapt to your needs. Sometimes it's better to create them from scratch so u know exactly how they do what they do.

    Happy programming!

Posting Permissions

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