set query parameter from VBA

[Gasman]

Hi everyone,
I have a query which at present shows all records.
I have created a listbox for the record types, Workday, Holiday etc.

I am happy with building whatever to pass to the query from the listbox but would appreciate some help in the syntax of amending a saved query.

I am using the ID of the record types and wish to use criteria like IN(15,16,17) if list entries for 15,16 & 17 were selected.

If I type in the the above criteria in the design window I get what I want, but how do I send that to the query?
I've tried a stored parameter and then using that whole string as the parameter, but Access says it is too complex or typed incorrectly.

So I need to vary the criteria with the IN clause.?
I also need to save it as I use it as a source for the report.

Doing it this way I hope to use one query and one report for multiple criteria?

How do I construct the parameter/query to accomplish this please?

Query SQL is currently

SELECT tblEmployeeDay.EmployeeID, nz(WeekdayName(Weekday([tblEmployeeDay].[DayDate],2),True),"") AS DayName, tblDates.DayDate, [tblEmployee.Forename] & " " & [tblEmployee.Surname] AS FullName, tblEmployeeDay.StartTime, tblEmployeeDay.EndTime, tblEmployeeDay.Lunch, IIf([DateType]=15 Or [DateType]=16,0,calctime([starttime],[endtime],[lunch])/60) AS Hours, tblEmployee.ReportsTo, [tblEmployee_1.Forename] & " " & [tblEmployee_1.Surname] AS Manager, tblLookup.DataValue, tblEmployeeDay.DateType, Year([tblEmployeeDay.DayDate]) & DatePart("ww",[tblEmployeeDay].[DayDate]) AS GroupDate
FROM tblDates INNER JOIN (((tblEmployee INNER JOIN tblEmployeeDay ON tblEmployee.EmployeeID = tblEmployeeDay.EmployeeID) INNER JOIN tblEmployee AS tblEmployee_1 ON tblEmployee.ReportsTo = tblEmployee_1.EmployeeID) INNER JOIN tblLookup ON tblEmployeeDay.DateType = tblLookup.LookupID) ON tblDates.DayID = tblEmployeeDay.DayID
WHERE (((tblEmployeeDay.EmployeeID)=[Forms]![frmEmployeeHoursRpt]![cboEmployeeID]) AND ((tblDates.DayDate) Between [Forms]![frmEmployeeHoursRpt]![txtStartDate] And [Forms]![frmEmployeeHoursRpt]![txtEndDate]) AND ((tblEmployeeDay.DateType) In (15,16)))
ORDER BY tblDates.DayDate, Year([tblEmployeeDay.DayDate]) & DatePart("ww",[tblEmployeeDay].[DayDate]);

TIA

 

[isladogs]

Hi Gasman

Try adapting this code for your multiselect listbox (courtesy of Allen Browne):

            strDelim = """"             'Delimiter appropriate to text field type.
            strCriteria = ""
            'Loop through the ItemsSelected in the list box.
            With Me.lstPupils
                For Each varItem In .ItemsSelected
                    If Not IsNull(varItem) Then
                        'Build up the filter from the bound column (hidden).
                        strCriteria = strCriteria & strDelim & .ItemData(varItem) & strDelim & ","
                    End If
                Next
            End With
    
            'Remove trailing comma. Add field name, IN operator, and brackets.
            lngLen = Len(strCriteria) - 1
            If lngLen > 0 Then
                strCriteria = "[PupilID] IN (" & Left(strCriteria, lngLen) & ")"
            End If

Also you might find the SQL to VBA converter useful. See:

https://www.access-programmers.co.uk/forums/showthread.php?t=293372

 

[Orthodox Dave]

Hi Gasman,

I am hesitant in replying when I look at your credentials, but even the most experienced can miss something. Forgive me if I'm stating the obvious here.

The way to pass parameters to saved queries via VBA is to use querydefs. I used this code on a saved query to pass a parameter into a document list.

The saved query included (in Query Design - the only way I know to code SQL) the criteria [CorID] under the field CorresID. Normally if you ran that query, a box would come up asking for the parameter value of [CorID]. I then used this code:

Set DB = CurrentDb()
 Dim DocListQDF As QueryDef
 Set DocListQDF = DB.QueryDefs("rstQ_DocList")
 DocListQDF.Parameters("CorID") = CorrID
 Set rstDocList = DocListQDF.OpenRecordset()

This is just the relevant bit. "CorrID" was a long integer variable.

Whether this would work with your IN(15,16,17) is up to you to decide.

 

[Gasman]

Hi Ridders,

I pretty much have that already

Private Sub cmdReport_Click()
Dim ctl As Control
Dim varItem As Variant
Dim strParam As String

Set ctl = Me!lstDayType
'Open the report with an OpenArgs value to get selected hours for employee
' Now select what type of day records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
    Next varItem
    strParam = Left(strParam, Len(strParam) - 1)
   ' do the qdf stuff here?
End If

Debug.Print strParam
Set ctl = Nothing

'DoCmd.OpenReport "rptHours", acPreview, , , , "Selected"
End Sub

Report is commented out for now

I've seen on here in the past something along the lines of
set qdf = querydefs("name of query")
qdf.Parameters("ParameterName") = strParam
and then the qdf is saved?
I am trying to work out the syntax to insert/change the citeria?

The reason I am doing it this way is because the source for the report can be changed on open depending on an argument to the report.
Initially the report only reported null hours (records to be processed) and can now show (records processed). I wish to tailor it further to show the selected types of records processed, be they Workdays, sick days etc.

Essentially I would like the report to run either without the IN clause (which would show me all the records for an employee within selected dates or with it (limiting the records to selected types). Is this possible?
If I can just get the construct of this IN clause correct and into the query I am there.

 

[Gasman]

Hi Dave,

I don't really have any credentials, I am an Access novice.

That is the sort of thing I was thinking of and if it was only for set parameters, one value to one parameter I would be able to do that, and have in the past.

However I am trying to construct the query so a field has the criteria of IN (15,16,17) where the '15,16,17' bit or the whole thing is passed in?

Whilst I only have a few types at the moment, they couild get more and it would then be along the lines of IN (14,15,16,19,32,45,56). That sort of thing.?

It is this bit that has got me stumped, or I am approaching it the wrong way, which has been known before.

Hi Gasman,

I am hesitant in replying when I look at your credentials, but even the most experienced can miss something. Forgive me if I'm stating the obvious here.

The way to pass parameters to saved queries via VBA is to use querydefs. I used this code on a saved query to pass a parameter into a document list.

The saved query included (in Query Design - the only way I know to code SQL) the criteria [CorID] under the field CorresID. Normally if you ran that query, a box would come up asking for the parameter value of [CorID]. I then used this code:

Set DB = CurrentDb()
 Dim DocListQDF As QueryDef
 Set DocListQDF = DB.QueryDefs("rstQ_DocList")
 DocListQDF.Parameters("CorID") = CorrID
 Set rstDocList = DocListQDF.OpenRecordset()

This is just the relevant bit. "CorrID" was a long integer variable.

Whether this would work with your IN(15,16,17) is up to you to decide.

 

[isladogs]

Is this any use?

Function CreateQuery(sQryName As String, sSQL As String)

'===============================================
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a new query in the current database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'==============================================

On Error Resume Next
    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef
 
    Set db = CurrentDb
 
    With db
        'In the next line we try and delete the query
        'If it exists it will be deleted, otherwise it will raise an error but since
        'we set our error handler to resume next it will skip over it and continue
        'with the creation of the query.
        .QueryDefs.Delete (sQryName)    'Delete the query if it exists
On Error GoTo Error_Handler             'Reinitiate our standard error handler
        Set qdf = .CreateQueryDef(sQryName, sSQL)    'Create the query
    End With
 
    db.QueryDefs.Refresh  'Refresh the query list to display the newly created query

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: CreateQuery" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

 

[Minty]

There are a couple of ways of defining the query maybe these simple examples will assist you;

Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("Parameter1") = Me.cmbFirstParam
qdf.Parameters("Parameter2") = Me.txtboxSecondParam 
qdf.Parameters("Parameter3") = Me.ListBoxParam3
qdf.Parameters("Parameter3") = strVariableString

etc, etc

Set rst = qdf.OpenRecordset


' ****************** VERSION 2
const SQL_INSERT as string = _
   "INSERT INTO tTable " & _
      "( numField, strField, datField ) " & _
   "VALUES " & _
      "( p0, p1, p2 ) "

with currentdb.createquerydef("", SQL_INSERT)
   .parameters("p0") = 123
   .parameters("p1") = "test"
   .parameters("p2") = #3/7/17#
   .execute dbFailOnError
   .close
end with

 

[Gasman]

Thank you Minty,

Seems it is not possible to do it the way I was expecting, judging from this link

https://stackoverflow.com/questions/33263553/vba-querydef-parameters

Time for a rethink. :banghead:

There are a couple of ways of defining the query maybe these simple examples will assist you;

Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("Parameter1") = Me.cmbFirstParam
qdf.Parameters("Parameter2") = Me.txtboxSecondParam 
qdf.Parameters("Parameter3") = Me.ListBoxParam3
qdf.Parameters("Parameter3") = strVariableString

etc, etc

Set rst = qdf.OpenRecordset


' ****************** VERSION 2
const SQL_INSERT as string = _
   "INSERT INTO tTable " & _
      "( numField, strField, datField ) " & _
   "VALUES " & _
      "( p0, p1, p2 ) "

with currentdb.createquerydef("", SQL_INSERT)
   .parameters("p0") = 123
   .parameters("p1") = "test"
   .parameters("p2") = #3/7/17#
   .execute dbFailOnError
   .close
end with

 

[CJ_London]

you can perhaps do what you want using the eval function

you'll need to modify your query criteria to

.....WHERE eval([myfield] & [parameter1])=true

your parameter to pass through

"in (15,16,17)"

Benefit is you can pass through other criteria such as =,<> etc

 

[Gasman]

Thank you Ridders,
I left work tonight thinking of a master query and taking the sql from that, substituting a known phrase for my criteria and creating a new query as your supplied code does.

CJ_London has supplied a method that should do what I was initially looking at doing. If I cannot get that to work, then I guess it will be back to building the sql on the fly.

Shame really as everything is in the query, just that IN phrase causing the problems.

All good learning experience nonetheless.

I expect to have time tomorrow to give it a try. I will post back my results.

Perhaps I should also ask, 'How would others here do the same thing?

Is this any use?

Function CreateQuery(sQryName As String, sSQL As String)

'===============================================
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a new query in the current database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'==============================================

On Error Resume Next
    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef
 
    Set db = CurrentDb
 
    With db
        'In the next line we try and delete the query
        'If it exists it will be deleted, otherwise it will raise an error but since
        'we set our error handler to resume next it will skip over it and continue
        'with the creation of the query.
        .QueryDefs.Delete (sQryName)    'Delete the query if it exists
On Error GoTo Error_Handler             'Reinitiate our standard error handler
        Set qdf = .CreateQueryDef(sQryName, sSQL)    'Create the query
    End With
 
    db.QueryDefs.Refresh  'Refresh the query list to display the newly created query

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: CreateQuery" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

 

[Cronk]

I find it easier to generate the sql string in the report's Open event

ie
<loop through list box based on selected lines and generate the string "(15,16)"
me.recordsource ="Select ... from ...." & " Where .... and ID in " & <yourstring> & " Order by ..."

You could update the query before opening the report by
set qdf = <yourQuery>
qdf.sql = <generated sql string as above>
set qdf = nothing

This way there is no need to delete the query and recreate it

 

[Gasman]

Hi Cronk,
TBH I try and stay away from lengthy sql strings unless absolutely necessarily due to my inexperience and generally generate them via the query design window, then modify to suit..
I do admit I am trying to achieve this with the minimum of change but also with a view to the future..
I know I could pass the criteria as an argument, in fact I pass an argument at the moment, but that is just to flag which query to run, A or B
Thinking about it last night that argument could just as easily be the query name.?

I realise that this is not the correct way to do things, but as I have no spec to start with and the DB is modified when I have time from other tasks, I pretty much build it up as I go along.
The report and query in question simply reported on one type of record. Now it would be nice to report on any selected types of record. Short sightedness perhaps, but as eveything takes me a while to create/work out how to do it, I only do what is required at the time. Sometimes things are never used. I created a letter creation DB with help from this site, and it has never been used, users happy to modify templates indivisually.

I do believe however that modifying the qdf is the way I would like to go.

I have CJ_London's EVAL solution to try out and also one of my own, which I think is slightly easier for me to implement and similar to what you have posted.

One thing I was going to ask in a new thread was this, which I think your solution addresses if it is a problem.?

If a user runs code to modify a qdf and another user is also running the same code, could the former user end up running the query with the latter users criteria, as the qdf is in the backend?

TIA

 

[Cronk]

"I ...generally generate them <sql strings. via the query design window"

Me too apart from the simplest. And it's not hard to change say,
... where myDate between #3/1/2017# and #3/31/2017# ...
into
where myDate between #" & format(forms!Myform!startDate,"mm/dd/yyyy") & "# etc

 

[CJ_London]

If a user runs code to modify a qdf and another user is also running the same code

providing each user has their own copy of the front end it should not matter since queries are stored in the front end

 

[Gasman]

Almost there:banghead:

I amended the sql for the query

PARAMETERS pInList Text ( 255 );
SELECT tblEmployeeDay.EmployeeID, nz(WeekdayName(Weekday([tblEmployeeDay].[DayDate],2),True),"") AS DayName, tblDates.DayDate, [tblEmployee.Forename] & " " & [tblEmployee.Surname] AS FullName, tblEmployeeDay.StartTime, tblEmployeeDay.EndTime, tblEmployeeDay.Lunch, IIf([DateType]=15 Or [DateType]=16,0,calctime([starttime],[endtime],[lunch])/60) AS Hours, tblEmployee.ReportsTo, [tblEmployee_1.Forename] & " " & [tblEmployee_1.Surname] AS Manager, tblLookup.DataValue, tblEmployeeDay.DateType, Year([tblEmployeeDay.DayDate]) & Format(DatePart("ww",[tblEmployeeDay].[DayDate]),"00") AS GroupDate
FROM tblDates INNER JOIN (((tblEmployee INNER JOIN tblEmployeeDay ON tblEmployee.EmployeeID = tblEmployeeDay.EmployeeID) INNER JOIN tblEmployee AS tblEmployee_1 ON tblEmployee.ReportsTo = tblEmployee_1.EmployeeID) INNER JOIN tblLookup ON tblEmployeeDay.DateType = tblLookup.LookupID) ON tblDates.DayID = tblEmployeeDay.DayID
WHERE (((tblEmployeeDay.EmployeeID)=[Forms]![frmEmployeeHoursRpt]![cboEmployeeID]) AND ((tblDates.DayDate) Between [Forms]![frmEmployeeHoursRpt]![txtStartDate] And [Forms]![frmEmployeeHoursRpt]![txtEndDate]) AND ((Eval([tblEmployeeDay.DateType] & [pInList]))=True))
ORDER BY tblDates.DayDate, Year([tblEmployeeDay.DayDate]) & Format(DatePart("ww",[tblEmployeeDay].[DayDate]),"00");

when run from the design window it asks for the parameter and supplies all the data instead.

However when run from the form using

strRptQuery = "qryEmployeeHours"
Set ctl = Me!lstDayType
'Open the report with an OpenArgs value to get selected hours for employee
' Now select what type of day records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
    Next varItem
  Else
    MsgBox ("At least one Day Type is required")
    Exit Sub
End If

' Need to pass the criteria as a string
strParam = "IN (" & Left(strParam, Len(strParam) - 1) & ")"
Debug.Print strParam
Set db = CurrentDb
Set qdf = db.QueryDefs(strRptQuery)
qdf.Parameters("pInList") = strParam

DoCmd.OpenReport "rptHours", acPreview, , , , "Selected"

it still asks for the parameter?, and if I ignore it and click, I still get the correct data?

pInList from the debug print is IN (13,15)

Even tried qdf.Close before running the report with same result.
What am I missing please?

 

[CJ_London]

looks like you are missing a space before IN in your calculation of the parameter

I did have it in my original post, but see it got lost when saved

 

[Gasman]

Hi CJ_London,
I've amended it to

strParam = " IN (" & Left(strParam, Len(strParam) - 1) & ")"

and it shows as

 IN (13)

in debug but still asks for the parameter. ?

Urghh, so close

 

[NauticalGent]

I had a similar issue but I am unsure if what I did was the reason it fixed itself. But since you are grasping at straws, what the hell.

Attached is a screenshot of what my query looked like. When I kept getting asked for a parameter, in a fit of desperation, I removed the parameters from the Query Parameters Box but left the query grid as is...

And it worked. Cant tell you why, but I categorized it as "PFM" and moved on.

Give it a try, what have you got to lose?

 

[Gasman]

Hi Nauticalgent,
If you mean remove the PARAMETERS statement altogether I have already tried that with the same result.

 

[NauticalGent]

Hi Nauticalgent,
If you mean remove the PARAMETERS statement altogether I have already tried that with the same result.

Not sure if we are singing the same song, but just to be clear, I left the parameters in my query but took them out of the parameter "pop-up" - the modal box that pops-up after you choose Parameters from the Query Design menu.

If that is what you did then apparently you need a different flavor of PFM...