;'     
    Public Function SQLPrep_c(ByVal acExpression)
     ' If Empty Expression
     If acExpression = "" Then
     ' Return Null
     SQLPrep_c = "NULL"
     ' Else expression has content
     Else
     ' Prepare For Errors
     On Error Resume Next
     ' Attempt To convert expression to Currency
     SQLPRep_c = CCur(acExpression)
     ' If Error occured
     If Err Then
     ' Clear Error
     Err.Clear
     SQLPrep_c = "NULL"
     End If ' Err
     End If ' acExpression = ""
    End Function ' SQLPrep_c
    '---------------------------------------
    '     ----------------------------------------
    '     
    Function buildJoinStatment(sTable,sFldLstAry,rs,conn)
    Dim i,sSql,sTablesAry,sJnFldsAry,bJoinAry,sJoinDisplay
    ReDim sTablesAry(UBound(sFldLstAry))
    ReDim sJnFldsAry(UBound(sFldLstAry))
    ReDim bJoinAry(UBound(sFldLstAry))
    For i = 0 To UBound(sFldLstAry)
    sSql = "SELECT OBJECT_NAME(rkeyid),COL_NAME(rkeyid,rkey1)"
    sSql = sSql &" FROM sysreferences"
    sSql = sSql &" WHERE fkeyid = OBJECT_ID('"& sTable &"') "
    sSql = sSql &" AND col_name(fkeyid,fkey1) = '"& Trim(sFldLstAry(i)) &"'"
    rs.open sSql,conn
    If Not rs.eof Then
    sTablesAry(i) = rs(0)
    sJnFldsAry(i) = rs(1)
    End If
    rs.close
    Next
    If UBound(sFldLstAry) >= 0 Then
    For i = 0 To UBound(sFldLstAry)
    If sTablesAry(i) <> "" Then
    bJoinAry(i) = True
    Else
    bJoinAry(i) = False
    End If
    If i <> UBound(sFldLstAry) Then sSql = sSql &" +' - '+ "
    Next
    sSql = "FROM "& sTable
    For i = 0 To UBound(sFldLstAry)
    If bJoinAry(i) Then sSql = sSql &" LEFT JOIN "& sTablesAry(i) &" ON "& sTable &"."& sFldLstAry(i) &"
= "& sTablesAry(i) &"."& sJnFldsAry(i)
    Next
    End If
    buildJoinStatment = sSql
    End Function
    '---------------------------------------
    '     ----------------------------------------
    '     
    Function buildQuery(ByRef asFieldAry, ByVal asKeyWords)
     ' To find fields that may have a word in them
     ' OR roger
     ' | roger
     ' roger
     ' To find fields that must match a word
     ' AND roger
     ' + roger
     ' & roger
     ' To find fields that must Not match a word
     ' Not roger
     ' - roger
     ' Also use phrases
     ' +"rogers dog" -cat
     ' +(rogers dog)
     Dim loRegExp
     Dim loRequiredWords
     Dim loUnwantedWords
     Dim loOptionalWords
     Dim lsSQL
     Dim lnIndex
     Dim lsKeyword
     Set loRegExp = New RegExp
     loRegExp.Global = True
     loRegExp.IgnoreCase = True
     loRegExp.Pattern = "((AND|[+&])s*[([{""].*[)]}""])|((ANDs|[+&])s*[-w']+)"
     Set loRequiredWords = loRegExp.Execute(asKeywords)
     asKeywords = loRegExp.Replace(asKeywords, "")
     loRegExp.Pattern = "(((NOT|[-])s*)?[([{""].*[)]}""])|(((NOTs+|[-])s*)[-w']+)"
     Set loUnwantedWords = loRegExp.Execute(asKeywords)
     asKeywords = loRegExp.Replace(asKeywords, "")
     loRegExp.Pattern = "(((OR|[|])s*)?[([{""].*[)]}""])|(((ORs+|[|])s*)?[-w']+)"
     Set loOptionalWords = loRegExp.Execute(asKeywords)
     asKeywords = loRegExp.Replace(asKeywords, "")
     If Not loRequiredWords.Count = 0 Then
     ' REQUIRED
     lsSQL = lsSQL & "("
     For lnIndex = 0 To loRequiredWords.Count - 1
     lsKeyword = loRequiredWords.Item(lnIndex).Value
     loRegExp.Pattern = "^(AND|[+&])s*"
     lsKeyword = loRegExp.Replace(lsKeyword, "")
     loRegExp.Pattern = "[()""[]{}]"
     lsKeyword = loRegExp.Replace(lsKeyword, "")
     lsKeyword = Replace(lsKeyword, "'", "'")
     If Not lnIndex = 0 Then
     lsSQL = lsSQL & " AND "
       End If
     lsSQL = lsSQL & "(" & Join(asFieldAry, " LIKE '%" & lsKeyword & "%' OR ")
& " LIKE '%" & lsKeyword & "%')"
     Next
     lsSQL = lsSQL & ")"
     End If
     If Not loOptionalWords.Count = 0 Then
     ' OPTIONAL
     If lsSQL = "" Then
     lsSQL = lsSQL & "("
     Else
     lsSQL = lsSQL & " AND ("
     End If
     For lnIndex = 0 To loOptionalWords.Count - 1
     lsKeyword = loOptionalWords.Item(lnIndex).Value
     loRegExp.Pattern = "^(OR|[|])s*"
     lsKeyword = loRegExp.Replace(lsKeyword, "")
     loRegExp.Pattern = "[()""[]{}]"
     lsKeyword = loRegExp.Replace(lsKeyword, "")
     lsKeyword = Replace(lsKeyword, "'", "'")
     If Not lnIndex = 0 Then
     lsSQL = lsSQL & " OR "
     End If
     lsSQL = lsSQL & "(" & Join(asFieldAry, " LIKE '%" & lsKeyword & "%' OR ")
& " LIKE '%" & lsKeyword & "%')"
     Next
     lsSQL = lsSQL & ")"
     End If
     If Not loUnwantedWords.Count = 0 Then
     ' UNWANTED
     If lsSQL = "" Then
     lsSQL = lsSQL & "NOT ("
     Else
     lsSQL = lsSQL & " AND Not ("
     End If
     For lnIndex = 0 To loUnwantedWords.Count - 1
     lsKeyword = loUnWantedWords.Item(lnIndex).Value
     loRegExp.Pattern = "^(NOT|[-])s*"
     lsKeyword = loRegExp.Replace(lsKeyword, "")
     loRegExp.Pattern = "[()""[]{}]"
     lsKeyword = loRegExp.Replace(lsKeyword, "")
     lsKeyword = Replace(lsKeyword, "'", "'")

上一页  [1] [2] [3] [4]  下一页