Avatar of MonteDelMar
 asked on

Word VBA - passing criteria to an Access Query

Structure of the Query
I would like to pass multiple parameters, from Word VBA, when prompted to [EnterCriteria] in the above query. For example, I might want results from all the departments listed in the database, or I may want only results for 'Engineering' and 'Development' ("Engineering" or "Development"). Is that possible or will I have to put the SQL query into my VBA code and pass the parameters that way?

Many thanks for your help.
DatabasesVBAMicrosoft Word

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon
Fabrice Lambert


Const dbFailOnError = 128
Const path As String = "c:\........."
Dim dbe As Object       '// DAO.DBEngine
Set dbe = CreateObject("DAO.DBEngine.120")  
Dim db As Object        '// DAO.Database
Set db = DAO.OpenDatabase(path, ReadOnly:=True)
Dim qd As Object        '// DAO.QueryDef
Set qd = db.QueryDefs("queryName")
qd.Parameters("EnterCriteria") = Value
qd.Execute dbFailOnError

Open in new window


Thanks for your reply, Fabrice.

I am using ADODB and this is what I have. I pass the name of the Query and the value of the Filter to the VBA routine as Strings. In this case it the query would be "qRateDisplay" and the filter would be the name of the department(s).

If I assign the value of the filter to be "Corporate" the query and code work.

If I assign the filter to be "Research & Development" or "Engineering", then it does not work.

How do I pass the string "Research & Development" or "Engineering"? This is my problem. I have tried messing about with the syntax but I have not found the answer.

    With cQuery
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = strQuery
        If strParameter <> "" And strFilter <> "" Then
            Set pParam = .CreateParameter(strParameter, adChar, adParamInput, Len(strFilter), strFilter)
            .Parameters.Append pParam
        End If

    End With

Open in new window

Many thanks.
Fabrice Lambert

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes