Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-29-2014, 02:38 AM   #1
BlueIshDan
 
Join Date: May 2014
Posts: 1,122
Thanks: 155
Thanked 160 Times in 156 Posts
BlueIshDan is on a distinguished road
Smile [FUNCTION] FindSimilarValues(table, field, compare_value, delimiter, match_per_cent)

FindSimilarValues(table_name, field_name, compare_value, delimiter, match_per_cent) As String()

This function will return an array of strings representing the values within a table's field that contained 0-100% of the passed string.

For Example:

Table: tblMaster
Field: ProjectName
Field Values:
- 1 test 3 4 5 6 7 8 9 10
- 1 test 3 4 5 6 7 test 9 10
- 1 2 3 4 5 test 7 8 9 10
- test test test test test test test test test test
- 1 2 test 4 5 test 7 8 test 10
- 1 2 test 4 5 6 7 8 9 10
- 1 test 3 4 test 6 7 test 9 10
- 1 2 test 4 5 6 7 8 9 10
- 1 2 3 4 5 6 7 test 9 10
- 1 2 3 4 5 test 7 8 9 test


Function: FindSimilarValues("tblMaster", "ProjectName", "test", " ", 30)

Results:
- test test test test test test test test test test
- 1 2 test 4 5 test 7 8 test 10
- 1 test 3 4 test 6 7 test 9 10


Example Use

Code:
    Dim msg As String
    For Each var_project_name In FindSimilarValues("tblMaster", "ProjectName", "test", " ", 30)
        msg = msg & var_project_name & vbNewLine
    Next
    MsgBox msg
Function

Code:
Private Function FindSimilarValues(ByVal table_name As String, _
                                    ByVal field_name As String, _
                                    ByVal compare_value As String, _
                                    ByVal delimiter As String, _
                                    ByVal match_per_cent As Integer) As String()
    
    Dim results() As String
    Dim results_count As Long: results_count = -1
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset(table_name)
    
    Dim matches As Integer
    Dim rs_word_count As Integer
    
    
    With rs
        While Not .EOF
        
            matches = 0
            rs_word_count = 0
            
            For Each var_rs_split In Split(.Fields(field_name), delimiter)
            
                rs_word_count = rs_word_count + 1
                
                For Each var_split In Split(compare_value, delimiter)
                
                    If var_rs_split = var_split Then
                    
                        matches = matches + 1
                        Exit For
                        
                    End If
                    
                Next
                
            Next
            
            If matches >= (rs_word_count * (match_per_cent / 100)) Then
                results_count = results_count + 1
                ReDim Preserve results(results_count)
                results(results_count) = .Fields(field_name)
            End If
            
            .MoveNext
        Wend
        
    End With
    
    FindSimilarValues = results
        
End Function
Regards,
BlueIshDan

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Blue's Age Poll:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Animated Sorting:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Mining File Directories:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GetSubFolders:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

GetFileLines:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

FindSimilarValues:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Move (Avoid moving objects game):
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by BlueIshDan; 05-30-2014 at 04:07 AM.
BlueIshDan is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to select field from a table using several criteria carmelina Modules & VBA 3 09-14-2011 11:50 AM
Running function on field and saving results to new table meadt Modules & VBA 2 11-30-2009 05:49 AM
help with delimiter aftershokk Queries 2 05-11-2009 05:01 AM
Table Field Not Accepting Data From Form Function COUNT. Lamont1701 Tables 2 10-07-2005 11:24 AM
Field Delimiter Amy Scivally General 2 05-28-2003 12:29 PM




All times are GMT -8. The time now is 04:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World