CODEWITHSUNDEEP
sqlms-accessvbams-access-2007
Jens
Jens

Reputation: 879

Multiple parameter values in one string in access SQL

I have an array which can have a different amount of values, depending on the situation. I want to put these values as a parameter in a query in ms access.

The problem is, if I use the following code to generate a parameter, it sends the whole string as one value to the query, which obviously does not return any rows.

Do Until i = size + 1
    If Not IsEmpty(gvaruocat(i)) Then
        If Not IsEmpty(DLookup("uo_cat_id", "tbl_uo_cat", "[uo_cat_id] = " & CInt(gvaruocat(i)))) Then
            If IsEmpty(get_uocat_param) Then
                get_uocat_param = CInt(gvaruocat(i))
            Else
                get_uocat_param = get_uocat_param & " OR tbl_uo_step.uo_step_cat = " & CInt(gvaruocat(i))
            End If
        End If
    End If
    i = i + 1
Loop

At the moment I have 'Fixed' it by generating an SQL string and leaving the query out all together.

get_uocat = "SELECT tbl_product.prod_descr, tbl_uo_cat.uo_cat_descr, tbl_uo_step.uo_step_descr" & vbCrLf _
          & "FROM (tbl_product INNER JOIN tbl_uo_cat ON tbl_product.prod_id = tbl_uo_cat.uo_cat_prod) INNER JOIN tbl_uo_step ON tbl_uo_cat.uo_cat_id = tbl_uo_step.uo_step_cat" & vbCrLf _
          & "WHERE (((tbl_uo_step.uo_step_cat) = " & get_uocat_param & ")) " & vbCrLf _
          & "ORDER BY tbl_product.prod_descr, tbl_uo_cat.uo_cat_descr, tbl_uo_step.uo_step_descr;"

This is however not very friendly to many changes. So my question is, how do I get the array to send each value as a separate parameter to the query?

Note: IsEmpty() is a custom function which checks for empty variables in case you were wondering.

Upvotes: 1

Views: 1312

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123594

You can still use a parameterized query in this case, despite your comment to the question. You just need to build the SQL string to include as many parameters as required, something like this:

Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
Dim sql As String, i As Long

' test data
Dim idArray(1) As Long
idArray(0) = 1
idArray(1) = 3

Set cdb = CurrentDb
sql = "SELECT [LastName] FROM [People] WHERE [ID] IN ("

' add parameters to IN clause
For i = 0 To UBound(idArray)
    sql = sql & "[param" & i & "],"
Next
sql = Left(sql, Len(sql) - 1) ' trim trailing comma
sql = sql & ")"
Debug.Print sql  ' verify SQL statement

Set qdf = cdb.CreateQueryDef("", sql)
For i = 0 To UBound(idArray)
    qdf.Parameters("param" & i).Value = idArray(i)
Next
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

' check results
Do Until rst.EOF
    Debug.Print rst!LastName
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set cdb = Nothing

When I run this on my test database I get

SELECT [LastName] FROM [People] WHERE [ID] IN ([param0],[param1])
Thompson
Simpson

Upvotes: 2

PaulFrancis
PaulFrancis

Reputation: 5809

you could make use of the IN Clause, instead. Which would work out better. 

Do Until i = size + 1
    If Not IsEmpty(gvaruocat(i)) Then
        If Not IsEmpty(DLookup("uo_cat_id", "tbl_uo_cat", "[uo_cat_id] = " & CInt(gvaruocat(i)))) Then
            If IsEmpty(get_uocat_param) Then
                get_uocat_param = CInt(gvaruocat(i))
            Else
                get_uocat_param = get_uocat_param & ", " & CInt(gvaruocat(i))
            End If
        End If
    End If
    i = i + 1
Loop

Then your Query build could use, 

get_uocat = "SELECT tbl_product.prod_descr, tbl_uo_cat.uo_cat_descr, tbl_uo_step.uo_step_descr" & vbCrLf _
              & "FROM (tbl_product INNER JOIN tbl_uo_cat ON tbl_product.prod_id = tbl_uo_cat.uo_cat_prod) INNER JOIN tbl_uo_step ON tbl_uo_cat.uo_cat_id = tbl_uo_step.uo_step_cat" & vbCrLf _
              & "WHERE ((tbl_uo_step.uo_step_cat IN (" & get_uocat_param & "))) " & vbCrLf _
              & "ORDER BY tbl_product.prod_descr, tbl_uo_cat.uo_cat_descr, tbl_uo_step.uo_step_descr;"

Upvotes: 0

Related Questions