Creating a where generator

[citychap26]

Hi

I am creating a where generator for a search form. Was wondering if anyone has done this. I have seen the FAQ before anyone jumps out of there seats, and want a different view. It works by sending any value in a form and creating the correct SQL syntax.

I have written a prototype which searches if there is an operator as the first char, then searches for an " AND " or an " OR " or a " LIKE ", then searches for a second operator.

Function checkValue(sString As String) As String

Dim bOperator As Boolean
Dim sValue As String
Dim bAND As Boolean
Dim bOR As Boolean
Dim bLIKE As Boolean

'// Check for AND/ OR in the string

'// search for ' OR ' rather than 'OR' due to the fact it would pick "OPERATOR" up as having an 'OR'
'// we only want to search for criteria1 OR criteria2

If InStr(sString, " OR ") > 0 Then
bOR = True
End If

' If Left(sString, 1) = "<" Or Left(sString, 1) = ">" Then
' '// we need to create an statement like x > y and x < z where x is the fieldname

If InStr(sString, " AND ") > 0 Then
bAND = True
End If

If InStr(sString, "LIKE") > 0 Then
bLIKE = True
End If


'look for greater than or less than operators, they will obviously be on the left hand side

sValue = Left(sString, 1)

Select Case sValue
Case "<"
bOperator = True
Case ">"
bOperator = True
Case "*"
bOperator = True
checkValue = " like '" & sString & "*'"
Exit Function
Case Else
bOperator = False
End Select

'// If bOR or bAND is true then there might be another operator present, check for it.

If bOperator And (bOR Or bAND) Then
'// If we have arrived here then we have an operator and an OR or an AND (god this gets complicated!)
'// now we need to check for a second operator.

Debug.Print "There is an operator and an 'and' or an 'or'"
ElseIf (bOR Or bAND) Then

Debug.Print "There is just an 'and' or an 'or'"

ElseIf bLIKE Then

Debug.Print "There is a 'like'"

ElseIf bOperator Then
checkValue = sString
Else
checkValue = " = " & sString
End If

End Function

Any Comments

Cheers

SK

 

[FancyPrairie]

Try this FAQ faq181-5497. It contains a function that will return the Where clause for you (without the word Where). You could use this to filter your form.