Error on query open

[Guest]

I have created a report that opens a query to supply totals on the 'On
Format' event of a report footer.
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "qryTotalRepairHours", Conn, adOpenDynamic,
adLockOptimistic
rst.Find "fldRepairedPartNumber = '" &
Me!txtRepairedPartNumber & "'"
Me!txtGrandTotal = rst![Grand Total]
Me!txtGrandCount = rst![Grand Count]
Me!txtCountOffldRepairedPartNumber =
rst![CountOffldRepairedPartNumber]
Me!txtTotalUnitRepairCost = rst![SumOffldUnitRepairPrice]
rst.Close
'Release Table and Connection
Set rst = Nothing
Set Conn = Nothing

There are actually 2 queries that feed one (qryTotalRepairHours). Each of
the queries use a date field. If I criteria the date with 'is notnull' (in
both feeding queries), it works fine and I get all the dates entered. If I
try to narrow the date by using 'between'(in both feeding queries) it gives
me an error and highlites the open statement.
rst.Open "qryTotalRepairHours", Conn, adOpenDynamic, adLockOptimistic

Any thoughts.

 

[Guest]

It's tough to say without seeing the queries, but in specifying the dates to
be used in the "between" query, did you flank the dates with the # character?

.... between #9/1/2005# and #9/5/2005#.....

 

[Guest]

--
Thanks for any assistance

It's tough to say without seeing the queries, but in specifying the dates to
be used in the "between" query, did you flank the dates with the # character?

... between #9/1/2005# and #9/5/2005#.....

I have created a report that opens a query to supply totals on the 'On
Format' event of a report footer.
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "qryTotalRepairHours", Conn, adOpenDynamic,
adLockOptimistic
rst.Find "fldRepairedPartNumber = '" &
Me!txtRepairedPartNumber & "'"
Me!txtGrandTotal = rst![Grand Total]
Me!txtGrandCount = rst![Grand Count]
Me!txtCountOffldRepairedPartNumber =
rst![CountOffldRepairedPartNumber]
Me!txtTotalUnitRepairCost = rst![SumOffldUnitRepairPrice]
rst.Close
'Release Table and Connection
Set rst = Nothing
Set Conn = Nothing

There are actually 2 queries that feed one (qryTotalRepairHours). Each of
the queries use a date field. If I criteria the date with 'is notnull' (in
both feeding queries), it works fine and I get all the dates entered. If I
try to narrow the date by using 'between'(in both feeding queries) it gives
me an error and highlites the open statement.
rst.Open "qryTotalRepairHours", Conn, adOpenDynamic, adLockOptimistic

Any thoughts.

 

[Guest]

Yes I did, I also created a form with a txtbox for starting and ending date
and used

Between [Forms]![frmMenuManagementTools]![txtBeginingDate] And
[Forms]![frmMenuManagementTools]![txtEndingDate]
same problem.
--
Thanks for any assistance

--
Thanks for any assistance

It's tough to say without seeing the queries, but in specifying the dates to
be used in the "between" query, did you flank the dates with the # character?

... between #9/1/2005# and #9/5/2005#.....

I have created a report that opens a query to supply totals on the 'On
Format' event of a report footer.
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "qryTotalRepairHours", Conn, adOpenDynamic,
adLockOptimistic
rst.Find "fldRepairedPartNumber = '" &
Me!txtRepairedPartNumber & "'"
Me!txtGrandTotal = rst![Grand Total]
Me!txtGrandCount = rst![Grand Count]
Me!txtCountOffldRepairedPartNumber =
rst![CountOffldRepairedPartNumber]
Me!txtTotalUnitRepairCost = rst![SumOffldUnitRepairPrice]
rst.Close
'Release Table and Connection
Set rst = Nothing
Set Conn = Nothing

There are actually 2 queries that feed one (qryTotalRepairHours). Each of
the queries use a date field. If I criteria the date with 'is notnull' (in
both feeding queries), it works fine and I get all the dates entered. If I
try to narrow the date by using 'between'(in both feeding queries) it gives
me an error and highlites the open statement.
rst.Open "qryTotalRepairHours", Conn, adOpenDynamic, adLockOptimistic

Any thoughts.