Error on query open

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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#.....
 
--
Thanks for any assistance


pvdalen said:
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#.....



NNlogistics said:
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.
Click to expand...
Click to expand...
 
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


NNlogistics said:
--
Thanks for any assistance


pvdalen said:
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#.....



NNlogistics said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top