Embedded SQL: Using a function in a WHERE clause.

[blakey2]

Hello All,

I have an embedded SQL statement in VBA (Access 2002). In my statement, I would like to write a simple VBA function which returns a boolean. Then I want to use this function as my argument for my WHERE clause.

The idea is that when the function evaluates/returns TRUE, the record will show in the query and when FALSE, it will not.

My code is a little messy, but here it is: (NOTE: Code works perfectly without the introduction of the WHERE clause)

__________________________________________
Public Function generateAllQuery(typeFlag As Integer)

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    Dim table1 As String
    Dim table2 As String
    Dim prodGrade As String
    Dim prodMod As String
    Dim qryNAME As String
    
    Dim result As Boolean
        
 ' Deal with the three type flags.
 ' I cut this code out as not important eg init. below...
    
        table1 = "tblColesStoresMasterList"
        table2 = "tblGarbageBags"
        qryNAME = "qryGarbageBags"
        prodGrade = "GBGrade"
        prodMod = "GBMods"
           
    Set db = CurrentDb
    Set qdf = db.QueryDefs(qryNAME)
    
 ' Below is summarised version of my query (should be the
 ' important bits.
        strSQL = " SELECT CS.*, P.* " & _
                "FROM " & table1 & " AS CS " & _
                " LEFT OUTER JOIN " & table2 & " AS P " & _
                "ON CS.StoreCode = P.StoreCode " & _
                "WHERE filterTable(CS." & prodGrade & ", P.Grade) " & _
                "ORDER BY CS.State, CS.StoreCode;"
        
    qdf.SQL = strSQL
    Set qdf = Nothing
    Set db = Nothing
    
End Function
_______________________________________________
Public Function filterTable(LCSGrade As String, LPGrade As String) As Boolean

    If LPGrade = C And (LCSGrade = C Or LCSGrade = B Or LCSGrade = A) Then
        filterTable = True
        End If
    ElseIf LPGrade = B And (LCSGrade = B Or LCSGrade = A) Then
        filterTable = True
        End If
    ElseIf LPGrade = A And LCSGrade = A Then
        filterTable = True
        End If
    Else
        filterTable = False
        End If
    End If
End Function
_________________________________________________

I am unsure if access/VBA requires the 'extra End If s' but I put them there when I was trying to bludgeon my way through the syntax.

Thanks - blakey2.

 

[PHV]

You may try this:
If LPGrade = "C" And (LCSGrade = "C" Or LCSGrade = "B" Or LCSGrade = "A") Then
filterTable = True
ElseIf LPGrade = "B" And (LCSGrade = "B" Or LCSGrade = "A") Then
filterTable = True
ElseIf LPGrade = "A" And LCSGrade = "A" Then
filterTable = True
Else
filterTable = False
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blakey2]

Hey PH,

Thanks for the reply, but my question was not really about the 'If' syntax.

I am having trouble getting the code to compile. The problem is the syntax of the WHERE clause specifically calling my function 'filterTable' which returns a boolean.

The code above does not compile. If I remove the WHERE clause entireley, then it does, but I don't filter my results as I would like to.

The only way in which I can get the code to compile is by placing the function call in single quotes. ie the entire line would be
"WHERE 'filterTable(CS." & prodGrade & ", P.Grade)' " & _

This compiles, but does not do the job, or even call the function. Presumably as it is treating the entire function call as a string?

I thought perhaps that the syntax would be along the lines of what I have in the code, but this does not work.

Any help/suggestions are appreciated.

Thanks - blakey2.

 

[PatrickIRL]

Because you are calling a function, I think you have to qualify the function call. So your strSQL statement should look like this :

"SELECT CS.*, P.* FROM " & table1 & " AS CS " & _
"LEFT OUTER JOIN " & table2 & " AS P " & _
"ON CS.StoreCode = P.StoreCode " & _
"WHERE {fn filterTable(CS." & prodGrade & ",P.Grade)} " & _
"ORDER BY CS.State, CS.StoreCode;"

Hope this helps,

Patrick

 

[PHV]

You may try this :
"WHERE filterTable(CS." & prodGrade & ", P.Grade)=True " & _
BTW, the filterTable function must be defined in a standard code module, not a form one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I would try not use any functions/code for this. I would create a table with the true or false values dependent on the LPGrade and LCSGrade values. Then just join this "lookup" table into your query.

Maintaining data is much easier than maintaining code and complex expressions.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hello All,

Thanks for the advice. Unfortuantely I could not get the syntax correct to call a function, but have resolved my dilemma. It is not a great way of working it out, and is the outcome I was trying to avoid.

I could not get the syntax correct, to call my function 'filterTable' so I ditched the function and embedded nested iif clauses. Messy, Ugly, difficult to maintain - YES. But advantageous given that it works.

Here she is:

strSQL=" SELECT CS.*, P.* " & _
      "FROM " & table1 & " AS CS " & _
      " LEFT OUTER JOIN " & table2 & " AS P " & _
      "ON CS.StoreCode = P.StoreCode " & _
      "WHERE " & _
      "iif(CS." & prodGrade & " = 'A' and " & _ 
         "(P.Grade = 'C' or P.Grade = 'B' or P.Grade = 'A'), True, " & _
      "iif(CS." & prodGrade & " = 'B' and " & _ 
         "( P.Grade = 'B' or P.Grade = 'C'), True, " & _
      "iif(CS." & prodGrade & " = 'C' and P.Grade = 'C', " & _
         " True, False ))) " & _
      "ORDER BY CS.State, CS.StoreCode;"

Those keen enough to bother will notice that the conditions of the iif clauses vary slightly then those I was using previously. -- The initail cases were back to front

In terms of generating a (static?) table to join together with, as suggested by Duane, I am uncertain if this would have been beneficial in my case, so I have opted (currently) for my rather messy solution.

Thankyou all for replying and if anyone else has cause to work out the WHERE syntax, I am curious to hear from them.

Thanks - blakey2.

 

[dhookom]

blakey2,
It's your call. I try to make my apps data driven. I assume there will be changes in logic, new codes added, and other moving targets. Allowing my users to make these changes is a nice solution.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[blakey2]

Hey Duane,
No, there won't really be any changes. It is possible that there will be a new code added, but it is extremeley unlikely. I was hoping to get the iif statements in a seperate function, to ease readability and maintainability, but could not.

Cheers - blakey2.

 

[PHV]

And what about this where clause ?
WHERE prodGrade IN ('A','B','C') And P.Grade IN ('A','B','C') And P.Grade >= prodGrade

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244