Open Recordset based on Parameter query with OR operator (1 Viewer)

[MrHans]

Hi all,

I am having issues with opening a recordset based on a query that requires two parameters.
My query has a parameter [Year] and a parameter [Partlist]

The partlist should be a string of Article codes seperated by the Or parameter.
The first parameter (Year) works fine, but when I add the Partlist parameter it doesn't find any records anymore.

The GetArticlesFromGroup returns the string of Article Numbers that I want.
I tried all kinds of variants of this result:
'PARTNR1' Or 'PARTNR2' Or 'PARTNR3'
"PARTNR1" Or "PARTNR2" Or "PARTNR3"
"'PARTNR1' Or 'PARTNR2' Or 'PARTNR3'"

How should this string look like when passing it as a parameter?

Public Function GetOrdersByDealerByYearTop10(intCategory As Integer, strYear As String) As String
On Error GoTo Err_GetOrdersByDealerByYearTop10

    'Declare variables
    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim rst             As DAO.Recordset

    Dim intCnt          As Integer
    Dim strQuery        As String
    Dim strDB           As String
    Dim strResult       As String

    'Initalize variables
    intCnt = 1

    'Determine what query to open
    If CInt(strYear) < 2017 Then
        strQuery = "qryXXXOrdersDealerYear"
        strDB = "XXX"
    Else
        strQuery = "qryYYYOrdersDealerYear"
        strDB = "YYY"
    End If

    'Open a Recordset based on SQL Statement
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQuery)
    
    qdf.Parameters("Year").Value = strYear
    qdf.Parameters("Partlist").Value = GetArticlesFromGroup(intCategory, strDB)

    Set rst = qdf.OpenRecordset()

    'Loop through records and generate dealerlist.
    rst.MoveFirst
    Do Until intCnt = 11 Or rst.EOF
        Debug.Print rst!Number & " - " & rst!Name
        intCnt = intCnt + 1
        rst.MoveNext
    Loop
    rst.Close

Exit_GetOrdersByDealerByYearTop10:
    Set rst = Nothing   'Clean-up
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

Err_GetOrdersByDealerByYearTop10:
    Resume Exit_GetOrdersByDealerByYearTop10

End Function

 

[CJ_London]

Depends on what your query is doing with the Year value but I would suggest pass strYear as an integer rather than a string and do the same in the parameters declaration of your query

Also Year is a reserved word - here is a link to reserved words https://support.office.com/en-us/ar...-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE

using them can cause issues.

At the very least, put square brackets around Year, both in your query (parameters and sql code plus qdf.Parameters("[Year]")

With regards to other list, you cannot write a query like that. if you write the query it would be

myfield='PARTNR1' Or myfield='PARTNR2' Or myfield='PARTNR3'

so needs 3 parameters

instead rewrite your query

eval("MyField IN (" & [PartList] & ")")=true

and your partlist needs to be comma separated i.e.

'PARTNR1', 'PARTNR2', 'PARTNR3'

 

[MrHans]

Thanks a lot CJ.

The year is actually stored as a string in this particular DB, so I made it a string deliberately.
And yes, changing the name of the parameter to a non-reserved word makes good sense.

I will try to modify the query to use the Eval function instead.
I think this was my main problem.