set query parameter from VBA

[Gasman]

No we are on the same hymn sheet.
Removing the parameter from the parameter dialog window gives

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");

and as you can see no PARAMETER clause exists now.

I have even changed the eval statement to have an additional space

Eval([tblEmployeeDay.DateType] & " " & [pInList])

and it still does not work.

I am going to try again on Monday.
I did manage to do it with copying the querydef amending the sql with a replace and then deleting it, but this way seemed tidier?

 

[NauticalGent]

I see...I'm afraid SQL is not my strong suite (not sure if I even have one!), because of that I ALWAYS use stored queries because of it. I have tried switching to SQL view and doing the cut and paste thingy - with marginal success - but as a rule my first option is stored queries.

NO shame in my game!

 

[Gasman]

BTW what is PFM please?

 

[CJ_London]

I might be sending you down a blind alley

I use the eval function on some of my more complex search forms where the user can type in >,<,<>,= etc (because it simplifies coding) and tried it for IN - which worked, but I do not set a parameter, I set a filter or build it into a sql query as a criteria. Note: this revisit has made me modify my construct slightly to include a space before the value in case the user hasn't entered one.

parameter values are set at the time of execution.

This code works

Parameters param text (255);
SELECT * FROM myTable WHERE eval([ID] & [Param])=true

at the time of opening you are prompted for the param value.

but when then opening the query at a later point i.e. when you open your report, those parameter values are lost and need to be reentered.

Suggest instead you use the where parameter for your openreport


DoCmd.OpenReport "rptHours", acPreview, , , "[DateType]" & strParam, "Selected"

and remove the parameters from your query

 

[NauticalGent]

Pure ____ Magic.

 

[Gasman]

Hi CJ_London,
Thank you.

I'll try that on Monday.

Everyone have a good weekend. I'm going to have a few beers. I feel I have earned them this week. -)

I might be sending you down a blind alley

I use the eval function on some of my more complex search forms where the user can type in >,<,<>,= etc (because it simplifies coding) and tried it for IN - which worked, but I do not set a parameter, I set a filter or build it into a sql query as a criteria. Note: this revisit has made me modify my construct slightly to include a space before the value in case the user hasn't entered one.

parameter values are set at the time of execution.

This code works

Parameters param text (255);
SELECT * FROM myTable WHERE eval([ID] & [Param])=true

at the time of opening you are prompted for the param value.

but when then opening the query at a later point i.e. when you open your report, those parameter values are lost and need to be reentered.

Suggest instead you use the where parameter for your openreport


DoCmd.OpenReport "rptHours", acPreview, , , "[DateType]" & strParam, "Selected"

and remove the parameters from your query

 

[CJ_London]

Pure ____ Magic.

what is???

 

[NauticalGent]

It is a term I use to explain that which is inexplicable.

 

[Orthodox Dave]

Have a great weekend Gasman. Weather in Swansea is forecast good for the weekend (for once!) so come back fresh on Monday!

 

[Gasman]

Have a great weekend Gasman. Weather in Swansea is forecast good for the weekend (for once!) so come back fresh on Monday!

I've deliberately not sent the DB home, so I cannot work on it over the weekend.

 

[static]

I haven't read the whole thread so apologies in advance if I missed something.

This is your sql with DateTypes replaced with a placeholder %DateType%

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 (%DateType%))
    )
ORDER BY tblDates.DayDate, Year([tblEmployeeDay.DayDate]) & DatePart("ww",[tblEmployeeDay].[DayDate]);

You have a sub that generates the value list.
To open a recordset, replace the placeholder with the values.

set rs = currentdb.openrecordset(replace(sql,"%DateType%", strParam))

Personally I would create stored queries for each table containing the calculated columns and link those. It would simplify this sql and any other queries you might write that need the same columns.

 

[Gasman]

Hi static,

The problem is that I am trying to amend a qdf to be used in a report, and the IN clause is proving difficult to overcome. I am not using a recordset as such.

I have already overcome it by being able to copy the qdf and replace placefolder of 999999 with the values i require, then delete the qdf after the report had run.

It is just that seems overkill and somewhat frowned upon from what I have read here and CJ_London's suggestion seemed tidier. There are plenty of threads advising having to create/delete qdfs on here.

I am going to try the filter option on the report on Monday and if that proves too difficult to achieve( for me at least), I will go back to that method.

I do like the way you structured the sql. Was that by hand?

I haven't read the whole thread so apologies in advance if I missed something.

This is your sql with DateTypes replaced with a placeholder %DateType%

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 (%DateType%))
    )
ORDER BY tblDates.DayDate, Year([tblEmployeeDay.DayDate]) & DatePart("ww",[tblEmployeeDay].[DayDate]);

You have a sub that generates the value list.
To open a recordset, replace the placeholder with the values.

set rs = currentdb.openrecordset(replace(sql,"%DateType%", strParam))

Personally I would create stored queries for each table containing the calculated columns and link those. It would simplify this sql and any other queries you might write that need the same columns.

 

[static]

Creating or changing stored queries on the fly is OK in a users own front end.
You shouldn't do it if the db is shared.

Not sure if you are still using IN or not but if so, and the db is used by a single user you would be better off storing the values in a temp table and linking the fields. Less sql. Better performance. A bit more bloat.

Yep. That's how SQL should look. It's the only way I can read it.

 

[Gasman]

Thank you CJ_London,

Got it working by your other suggestion.

strParam = "[DateType]" & strParam
DoCmd.OpenReport "rptHours", acViewReport, , strParam, , "Selected"

I might be sending you down a blind alley

I use the eval function on some of my more complex search forms where the user can type in >,<,<>,= etc (because it simplifies coding) and tried it for IN - which worked, but I do not set a parameter, I set a filter or build it into a sql query as a criteria. Note: this revisit has made me modify my construct slightly to include a space before the value in case the user hasn't entered one.

parameter values are set at the time of execution.

This code works

Parameters param text (255);
SELECT * FROM myTable WHERE eval([ID] & [Param])=true

at the time of opening you are prompted for the param value.

but when then opening the query at a later point i.e. when you open your report, those parameter values are lost and need to be reentered.

Suggest instead you use the where parameter for your openreport


DoCmd.OpenReport "rptHours", acPreview, , , "[DateType]" & strParam, "Selected"

and remove the parameters from your query

 

[apr pillai]

You may go through the following link, explaining several simple ways to submit multiple parameters (including IN(SELECT Field FROM ParamQuery)) in Criteria Row of the Main Query:

Multiple Parameters for Query

 

[Gasman]

Thank you apr. As I now have it working, I am going to leave it well alone and only come back to it, should I need to.

You may go through the following link, explaining several simple ways to submit multiple parameters (including IN(SELECT Field FROM ParamQuery)) in Criteria Row of the Main Query:

Multiple Parameters for Query