Modify Query for a Report

[ktrasler]

Hi all

I have a report rptTeamPickStats which source is a query qryTeamPickStats

The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.

I want to be able to modify the where using VB so I don't have to have several queries.

The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.

I want to query all the TLName where the date is between to dates.

    DoCmd.OpenReport myReport, acViewPreview, "", _
    "[TLName]='" & myFilter & "' And qryTeamPickStats!Date >= #" & myFromDate & "# And qryTeamPickStats!Date <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"

Hope this makes sense

Cheers

Kev.

 

[Moniker]

Make the variables myFromDate and myToDate Global. Therefore, when they get changed anywhere in the program, then change is propogated throughout all the objects that reference it. Make sure you make them global in a module, not in the code behind a form or report. Otherwise, they aren't truly global. If you don't have any true modules, it's fine to have a module with just the global variable declarations in it.

 

[ktrasler]

Moniker

Thanks for you reply, I can't seem to get it to work though.

I have made myFromDate and myToDate global as you said.

How do I reference the variable in the query.

>=[myfromdate] And <[mytodate]-1

I had entered it like this but when I run the query or report it says enter parameter value.

DoCmd.OpenReport myReport, acViewPreview, "", _
    "[TLName]='" & myFilter & "' And [OTcode] " & myOp & " 'NA'"

I have modified the code and took out the date part, is this any closer.

Cheers

Kev.

 

[ktrasler]

I was reading somewhere else stating that you had to use the .tag when modifying queries in vba.

I have tried now to assign the form.tag to the date criteria as below

 Me.Tag = ">= #" & myFromDate & "# And < #" & myToDate & "#"

Then in the query I have put the following expression in the Date field

[Forms]![frmREVIEWSTATS].[tag]

I would love to say it works.......but it don't.

Am I going about this completely the wrong way. I thought this would be fairly simple.

Any help appreciated.

Cheers

Kev.

 

[Krava]

Hi, Kev!

I was fighting with similar problem and I have used simple method how to change the data source of the form/report based on query. For me it was the most reasonable solution:
You can modify the Query SQL by code, so if your Query is qryTeamPickStats I would do:

Dim db As Database
Dim QryDef As QueryDef

Set db = CurrentDb
Set QryDef = db.QueryDefs("MakeUpCriticalListInternal")

QryDef.SQL = "Input your SQL statement with desired parameters of the date from your selection form"

QryDef.Close
db.Close

You can modify your query this way before opening the report or when report is open (I believe) and then just refres your report.

Hope I understood correctly.

Krava

 

[ktrasler]

Hi Krava

Thank you for you help, the code below does the job PERFECT.

Sub modQuery()

Dim db As Database
Dim QryDef As QueryDef

Set db = CurrentDb
Set QryDef = db.QueryDefs("qryTeamPickStats")

QryDef.SQL = "SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.TLName, qryColleagues.Shift, " & _
"Sum(tblPerformance.PickHours) AS SumOfPickHours, Sum(tblPerformance.PickCases) AS SumOfPickCases, Sum(tblPerformance.PickAssn) AS SumOfPickAssn, " & _
"tblPerformance.OTCode FROM qryColleagues INNER JOIN tblPerformance ON qryColleagues.WCN = tblPerformance.WCN " & _
"WHERE (((tblPerformance.Date)>=(Date()+1-Weekday(Date(),1)-91) And (tblPerformance.Date)<=(Date()+1-Weekday(Date(),1)-1)))" & _
"GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.TLName, qryColleagues.Shift, tblPerformance.OTCode " & _
"HAVING (((Sum(tblPerformance.PickHours))>0));"

QryDef.Close
db.Close

End Sub

Is it possible to do the above without having to specify the entire sql. i.e just modify the where part.

Not to worry if not.

Thanks again for your help on this.

Cheers

Kev.