Using Code to create criteria in query (1 Viewer)

[sxchopr]

Hi Everyone,
I'm creating a new database in which I'm trying to use VB to create criteria for use in query. But no luck. Here is what I have so far

Sub Array_Query()
MyQry(0) = "In ('0014705','0014710','0014715')":
End Sub

Function GL_Code(i As Integer)
On Error GoTo err_gl

Dim MyDB As Database
Set MyDB = CurrentDb
Call Array_Query

GL_Code = MyQry(i)

Exit Function
err_gl:
MsgBox Err.DESCRIPTION
Exit Function


End Function


I then put GL_Code(0) into the criteria of a query but it does not work. Any help would be apprieciated.

Sandman

 

[Mile-O]

Have a look at this.

 

[sxchopr]

Thanks, but that does not help me. I need a method where I can store the criteria's and call them into the many queries I'm going to create. The reason for wanting it this way is the criteria's change often, with this approach I would be able to change the criteria in one location and have it flow to all my queries.

Sandman

 

[Mile-O]

Actually, it does help you - you just can't see how.

What you are currently trying to do is store criteria within string variables. This will cause the problem that when you try to involve them within queries they will be viewed literally.

i.e.


WHERE MyField = "MyArray(0)"

and not

WHERE MyField = MyArray(0)

This means that you can't do it in the method you wish.

Therefore, since your criteria changes continuously, you have to create a dynamic query definition and use the variable within the building of the SQL.

Rather than use an array, however, I'd use a string constant due to the greater efficiency these offer once compiled.

An example being:

Option Explicit
Option Compare Database

Public Const Criteria1 As String = "In (""x"", ""y"", ""z"")"
Public Const Criteria2 As String = "Like ""*"" & [Forms]![MyForm]![MyControl] & ""*"""

Public Sub CreateQuery
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
   
    ' quick example code
    strSQL = "SELECT * FROM MyTable WHERE MyField "
    If [i]someValue[/i] = True Then
        strSQL = strSQL & Criteria1 & ";"
    Else
        strSQL = strSQL & Criteria2 & ";"
    End If

    ' remember to refer to the code in the example I 
    ' posted on how to create the stored query definition
    ' from the SQL string


    strSQL = vbNullString
    Set qdf = Nothing
    Set rs = Nothing
    Set db = Nothing  
 
End Sub

Of course, giving your users the chance to select their own criteria is always a better option.