DAO QueryDef DateRange

[Al Camp]

I want to apply a Date Range parameter to a query in DAO. frmDialog (Open)
calls this procedure, and has two dates on it that I want to use as parameters for
the query (BeginningDate and EndingDate)
Right now, I'm just trying to open the query, apply the parameters, and begin
operating upon that dataset.

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTestSnapReportOutage")
' query contains IncDate field...
' Parameters?? IncDate between Forms!frmDialog!BeginningDate
' and Forms!frmDialog!EndingDate
Set rst = qdf.OpenRecordset("qryTestSnapReportOutage")
rst.MoveLast
rst.MoveFirst

Just can't seem to get that parameter statement right.
Searched Google groups, but can't find this specific "using the form date range"
example.

Thanks in advance,
Al Camp

 

[Wayne Morgan]

If this doesn't help, you'll need to post the SQL view of the query.

For dates to work properly as parameters, you need to define them as dates.
Open the query in design view then go to Query|Parameters... on the menu
bar. Copy and paste the parameter from the query into the Parameter column
of the dialog box. Set the Type to Date/Time. Do this for each parameter. If
you use copy and paste, you'll have to copy them one at a time then open the
dialog, paste it, then close the dialog to copy the next one. You can also
just type them in, but they must be typed in EXACTLY the way they are in the
query.

 

[Al Camp]

Wayne,
Thanks for the reply... I think I mispoke... it's a date range "Criteria" aginst
[IncDate] that looks to my open form for Start/End dates.
Here's the query (qryTestSnapReportOutage) and the form that has the date range is
frmMaintenanceReportsDialog...(open)

SELECT tblLoomRepairs.IncidentDate AS IncDate, tblLoomRepairs.LoomNumber AS LoomNo,
[IncidentDate]+[IncidentTime] AS IncStartDT, [RepairEndDate]+[RepairEndTime] AS IncEndDT,
CDate("23:59:59") AS Midnight, tblLoomRepairs.TypeCode, tblMaintenanceIssues.SnapCode,
Max(DateDiff("n",[IncidentDate]+[IncidentTime],[RepairEndDate]+[RepairEndTime])/60) AS
OutageHrs
FROM tblLoomRepairs LEFT JOIN tblMaintenanceIssues ON tblLoomRepairs.Issue =
tblMaintenanceIssues.Issue
GROUP BY tblLoomRepairs.IncidentDate, tblLoomRepairs.LoomNumber,
[IncidentDate]+[IncidentTime], [RepairEndDate]+[RepairEndTime], CDate("23:59:59"),
tblLoomRepairs.TypeCode, tblMaintenanceIssues.SnapCode, tblMaintenanceIssues.Snap
HAVING (((tblLoomRepairs.IncidentDate) Between
[Forms]![frmMaintenanceReportsDialog]![BeginningDate] And
[Forms]![frmMaintenanceReportsDialog]![EndingDate]) AND ((tblLoomRepairs.LoomNumber)<>0)
AND ((tblMaintenanceIssues.Snap)=True))
ORDER BY tblLoomRepairs.LoomNumber, [IncidentDate]+[IncidentTime];

If I "hardwire" a date range in the query, (#1/1/05# to #1/31/05#) I can open the
recordset via DAO, but I need to adjust my code to bring in the "form's" values.

Thanks for any assistance... my first attempts at using DAO.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Wayne Morgan]

No, it appears that I understood you correctly. Doing the same thing I
mentioned directly in the SQL would look like:

PARAMETERS [Forms]![frmMaintenanceReportsDialog]![BeginningDate] DateTime,
[Forms]![frmMaintenanceReportsDialog]![EndingDate] DateTime;
SELECT ... etc

Try adding the above line (it is one line, the news reader probably wrapped
it) before the SELECT part of the SQL.

--
Wayne Morgan
MS Access MVP

Wayne,
Thanks for the reply... I think I mispoke... it's a date range
"Criteria" aginst [IncDate] that looks to my open form for Start/End
dates.
Here's the query (qryTestSnapReportOutage) and the form that has the date
range is frmMaintenanceReportsDialog...(open)

SELECT tblLoomRepairs.IncidentDate AS IncDate, tblLoomRepairs.LoomNumber
AS LoomNo, [IncidentDate]+[IncidentTime] AS IncStartDT,
[RepairEndDate]+[RepairEndTime] AS IncEndDT, CDate("23:59:59") AS
Midnight, tblLoomRepairs.TypeCode, tblMaintenanceIssues.SnapCode,
Max(DateDiff("n",[IncidentDate]+[IncidentTime],[RepairEndDate]+[RepairEndTime])/60)
AS OutageHrs
FROM tblLoomRepairs LEFT JOIN tblMaintenanceIssues ON tblLoomRepairs.Issue
= tblMaintenanceIssues.Issue
GROUP BY tblLoomRepairs.IncidentDate, tblLoomRepairs.LoomNumber,
[IncidentDate]+[IncidentTime], [RepairEndDate]+[RepairEndTime],
CDate("23:59:59"), tblLoomRepairs.TypeCode, tblMaintenanceIssues.SnapCode,
tblMaintenanceIssues.Snap
HAVING (((tblLoomRepairs.IncidentDate) Between
[Forms]![frmMaintenanceReportsDialog]![BeginningDate] And
[Forms]![frmMaintenanceReportsDialog]![EndingDate]) AND
((tblLoomRepairs.LoomNumber)<>0) AND ((tblMaintenanceIssues.Snap)=True))
ORDER BY tblLoomRepairs.LoomNumber, [IncidentDate]+[IncidentTime];

If I "hardwire" a date range in the query, (#1/1/05# to #1/31/05#) I can
open the recordset via DAO, but I need to adjust my code to bring in the
"form's" values.

Thanks for any assistance... my first attempts at using DAO.