Miscellaneous functions for SQL

[Aristarco]

thread705-74658

In 2001 I said here I had 2 functions I made to ease the creation of SQL strings. Then forgot it and now I come back -12 years later!- and find some of you wanted the functions. So I better post them before forgetting it and returning in 2025, ok?

'***** Find separator symbols in a string. Sort of a customizable ispunct() *****
Public Function IsSeparator(Caracter As Variant) As Boolean
Caracter = UCase(Caracter)
Select Case Caracter
    Case ",", ".", "-", "(", ")", ":", ";", "#", "¿", "?", "¡", "!" '** Add " " (space) if needed
        IsSeparator = True
    Case "[", "{", "}", "]", "\", "/", "'", "_", Chr(34) '** Chr(34) is double quotes ("), 
        IsSeparator = True
    Case Else
        IsSeparator = False
End Select
End Function

'***** Make SQL string from a collection of words *****
Public Function MakeSQL(InputPhrase, Field2Use As Variant) As Variant
Dim NewSpace, LastOne, LastSpace, n As Integer
Dim CurrCar, FirstChar, LastChar, Word As String
Dim OutputPhrase As String


   '*** Variable Cleanup
    NewSpace = -1: LastSpace = -1: LastOne = Len(InputPhrase)
    Word = "": FirstChar = ""
    '*** Advance thru the chars of the phrase, checking for separators
    For n = 1 To LastOne
        CurrCar = Mid$(InputPhrase, n, 1)
        '*** If current char is separator or the last in a string
        '*** set the word boundaries
        If IsSeparator(CurrCar) Or (n = LastOne) Then
            LastSpace = NewSpace + 1
            If n = LastOne Then
                NewSpace = n
            Else
                NewSpace = n - 1
            End If
            
            'Find first char of word
            FirstChar = Mid$(InputPhrase, (LastSpace + 1), 1)
            
            'If first character is not alpha, then it's not written right or it's a digit
            'or has a symbol attached, like parentheses or square brackets.
            'Advance one character to ignore the symbol.
            If IsSeparator(FirstChar) Then GoTo SkipHere
            
            'If not, keep checking if word has attached another symbol,
            'comma, period, etc., and remove it if necessary.
            LastChar = Mid$(InputPhrase, (NewSpace), 1)
            If IsSeparator(LastChar) Then
                Word = Mid$(InputPhrase, LastSpace + 1, (NewSpace - LastSpace) - 1)
            Else
                Word = Mid$(InputPhrase, LastSpace + 1, (NewSpace - LastSpace))
            End If
        'Generate OR string
            If OutputPhrase <> "" Then
                OutputPhrase = OutputPhrase & " OR [" & Field2Use & "] Like '*" & Word & "*'"
            Else
                OutputPhrase = "[" & Field2Use & "] Like '*" & Word & "*'"
            End If
        End If
SkipHere:
    Next n
    MakeSQL = OutputPhrase
End Function

MakeSQL("Red/Green/Blue", "Color") returns

[Color] Like '*Red*' OR [Color] Like '*Green*' OR [Color] Like '*Blue*'

I used this function with a text box in a form to find some records based on the words an analyst remebered the record contained. Then I added this to the WHERE part of the main SQL string.
I have a twin function named MakeNOTSQL() that only changes this part:

        'Generate AND NOT string
            If OutputPhrase <> "" Then
                OutputPhrase = OutputPhrase & " AND [" & Field2Use & "] Not Like '*" & Word & "*'"
            Else
                OutputPhrase = "[" & Field2Use & "] Not Like '*" & Word & "*'"
            End If
        End If

It's been 12 years but here it is! Hope it's useful for anyone.

To boldly code, where no programmer has compiled before!

 

[kjv1611]

Hey, maybe you should post this as an FAQ, or at least as a thread-type of tip rather than question, since I suppose you're not asking for any help with these. You could probably red-flag it yourself, and ask them to change the type to tip, I'd imagine.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

Or

[blue]Public Function MakeSQL(InputPhrase As String, Field2Use As String, Seperator As String) As String
    MakeSQL = "[" & Field2Use & "] LIKE '*" & Join(Split(InputPhrase, Seperator), "*' OR [" & Field2Use & "] LIKE '*") & "'"
End Function[/blue]

Called as

MakeSQL("Red/Green/Blue", "Color","/")

 

[Aristarco]

kjv1611: I put the lightbulb that makes it a tip. Is it not there? 12 years of not using this forum made me rusty. Thanks for the advice!
strongm: Whoa! Awesome! Never used Join() nor Split() functions. It's been a long time since I programmed in Access. My area shut down that job function and I never revised the old functions since I seldom use them. But hey, that's a great mod! Saves lots of code, thanks!

PS: I'm trying to remember and I think Access didn't have join() or split() back then. Or maybe I was too blind to see 'em. I made IsSeparator because my boss wanted to make the separator list customizable. Then again, thanks a lot!

To boldly code, where no programmer has compiled before!

 

[PHV]

I think Access didn't have join() or split()
Not prior ac2k

 

[Aristarco]

PHV: That's it, then! We were using Access 97 (was it 97?) until the office mongers acquired Access 2000 around year 2005. Then they cancelled any work in Access.

To boldly code, where no programmer has compiled before!