Managing And's & Or's in a SQL statement

[Iainkerr01]

I am creating a query system for my users so that they can generate a result set for themselves.
The form has 4 columns as follows.
1. the 'Join' field (ie blank, and, or)
2. the search field name
3. the condition field (ie = , <>, Like)
4. the value of the field to search on.

The form will have up to 4 rows allowing the uses to create a more complex query involving "and's" & "or's". The query may not necessarily have all 4 rows filled.

The information from each of the rows will be added together to form a SQL statement.

The 'Join' field from row 1 will always be an 'And' as this will be linked to a general 'Where' statement.

Does anyone have a module already written which will help me manage the necessary bracketing within the SQL statement particularly where 'ors' are used?

Thanks in advance.

 

[mikej336]

Here is a basic example of what you want to do... Some hard set and some conditional.

sqlstr = "SELECT ACCTGB, PRFXGB, YRGB, MOGB, SRCEGB, DESCGB, AMTGB, '' as test, COMPANYGB, COMPSUBGB, RCGB"
SUBSCONSGB
sqlstr = sqlstr & " FROM " & CurrLib & ".glyjrnpf4 inner join " & CurrLib & ".glyledpf3 on"
sqlstr = sqlstr & " companygb = coga and"
sqlstr = sqlstr & " compsubgb = comsubga and"
sqlstr = sqlstr & " yrgb = yrga and"
sqlstr = sqlstr & " acctgb = acctga and"
sqlstr = sqlstr & " prfxgb = prfxga"
sqlstr = sqlstr & " WHERE chtga <> 998"
sqlstr = sqlstr & " and YRGB = " & strYear

If Len(Trim(strSubsid)) > 0 Then
sqlstr = sqlstr & " and COMPSUBGB = " & strSubsid
End If

If Len(Trim(strFromAcct)) > 0 And Len(Trim(strToAcct)) > 0 Then
sqlstr = sqlstr & " and ACCTGB >= " & strFromAcct
sqlstr = sqlstr & " and ACCTGB <= " & strToAcct
ElseIf Len(Trim(strFromAcct)) > 0 Then
sqlstr = sqlstr & " and ACCTGB = " & strFromAcct
End If

If Len(Trim(strFromMonth)) > 0 And Len(Trim(strToMonth)) > 0 Then
sqlstr = sqlstr & " and MOGB >= " & strFromMonth
sqlstr = sqlstr & " and MOGB <= " & strToMonth
ElseIf Len(Trim(strFromMonth)) > 0 Then
sqlstr = sqlstr & " and MOGB = " & strFromMonth
End If

sqlstr = sqlstr & " Order by ACCTGB, PRFXGB, YRGB, MOGB, SRCEGB"
'Debug.Print sqlstr
Set adoprimaryrs = cn.Execute(sqlstr)

Hope it helps

Mike