Passing parameters

[robinsql]

Hi,

I am using a simple query...
SELECT * FROM PerfectAttendance

PerfectAttendance is a query I have saved. I run this query prior to selecting all from PerfectAttendance using the following...

sSQL = "Exec PerfectAttendance #" & Format(dtFromDate, "yyyy/mm/dd&quot & "#, #" & Format(dtFromDate, "yyyy/mm/dd&quot & "#, " & nYear_ID & ", " & iNumClockIns

The problem is that when I select all from PerfectAttendance I am prompted again for the parameters, which is no use to me.

How can I get around this?
Thanks,
Robin

 

[TonyJollans]

Hi robinsql,

The results of your (first) Query are not saved; as soon as you run another Query based on it, it is run again (as you have found out). Both queries produce the same result, so why not just dispense with one of the runs?

Where do you want the results of your Query? Can you post a few more details?

Enjoy,
Tony

 

[robinsql]

Hey Tony,
Thanks for reply.

What I am trying to produce is an SQL string I can pass to an Access report and the user will not be prompted for params.
The final result set I am looking for is the set of pupils who have clocked in twice a day, every day, for a given date range.
These are the three queries I am using.

ClockCount -
SELECT Count(tblTimes.FacilStuID) AS NumClockIns, tblTimes.FacilStuID, tblTimes.Date
FROM tblTimes INNER JOIN tblCalendar ON tblTimes.Date = tblCalendar.Date
WHERE (((tblCalendar.Description)='SCH') AND ((tblTimes.SetID)='2003/2004') AND ((tblTimes.Date)>=[FromDate] And (tblTimes.Date)<=[ToDate]))
GROUP BY tblTimes.FacilStuID, tblTimes.Date;

TotalClockIns -
SELECT SUM(NumClockIns) AS TotalNumClockIns, ClockCount.FacilStuID, tblPupil.Forename, tblPupil.Surname, tblPupil.Class
FROM ClockCount INNER JOIN tblPupil ON ClockCount.FacilStuID = tblPupil.FacilStuID
WHERE NumClockIns = 2
AND tblPupil.Year = YearNumber
GROUP BY ClockCount.FacilStuID, tblPupil.Forename, tblPupil.Surname, tblPupil.Class;

PerfectAttendance -
SELECT FromDate AS FromDate, ToDate AS ToDate, * FROM TotalClockIns WHERE TotalNumClockIns = NumClockIns ORDER BY Surname

How can I jsut call the last one once, and pass params to it?
Is this any clearer? Prob not!

Robin

 

[TonyJollans]

Hi Robin,

If your Report is based on the PerfectAttendance Query (without criteria), then you can pass criteria to it when you run it ..

DoCmd.OpenReport

ReportName

, , ,

criteria

.. where criteria is a WHERE clause (without the keyword WHERE)

Does that help at all?

Enjoy,
Tony

 

[robinsql]

Thanks for your help Tony.

I kind of cheated. I created a new table and inserted everything from the PerfectAttendance query into it.

Easy to run the report then!

Cheers,

Robin