You cancelled the previous operation.

M

mrs

I get "You cacelled the previous operation." error when I
try to run a query.

I have a form with two drop down boxes, a begin and a end
date and a button that runs a query. The query looks at
the form to determine what to display.

Here is the SQL for the query:

SELECT tblEvents.StartDate, tblEvents.ProductID,
tblEvents.SubCategorieID, tblEvents.AssignedToID,
tblEvents.EmployeeID, tblEvents.CompletedByDate,
tblEvents.CompletedByID, tblEvents.Discrepancy,
tblEvents.Resolution, tblEvents.VerifiedByID,
tblEvents.VerifiedByDate, tblEvents.PriorityID,
tblEvents.ProblemTypeID, tblEvents.VersionID,
tblEvents.EventID, tblEvents.Subject
FROM tblEvents
WHERE (((tblEvents.StartDate) Between DatePart("m",
[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" & DatePart
("d",[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" &
DatePart("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4])
And DatePart("m",[forms]![frmStrtDtStrBy]!
[dtstartDate4_2]) & "/" & DatePart("d",[forms]!
[frmStrtDtStrBy]![dtstartDate4_2]) & "/" & DatePart
("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4_2])) AND
((tblEvents.ProductID)=[forms]![frmStrtDtStrBy]!
[cmbProductName4]) AND ((tblEvents.SubCategorieID)=
[forms]![frmStrtDtStrBy]![comboSubCat4]))
ORDER BY tblEvents.StartDate, tblEvents.AssignedToID;

FYI: I have used the "Between DatePart" of the WHERE
clause in other queries and they work.

And here is the VB script for the button:


Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "fqryProSubCatDate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

Any ideas?

Thanks,

Matt
 
M

mrs

I forgot to mention: if I run the query without the form
being open pop-up messages ask for the product id, sub
categorie and dates and works. It works without using
the form.
 
G

Gary Walter

Hi Matt,

A "date" typically needs to be delimited by "#"'s in a
query.
WHERE (((tblEvents.StartDate) Between "#" & DatePart("m",
[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" & DatePart
("d",[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" &
DatePart("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4]) & "#"
And "#" & DatePart("m",[forms]![frmStrtDtStrBy]!
[dtstartDate4_2]) & "/" & DatePart("d",[forms]!
[frmStrtDtStrBy]![dtstartDate4_2]) & "/" & DatePart
("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4_2])) & "#"
Click to expand...

or set type in PARAMETERS clause

PARAMETERS [forms]![frmStrtDtStrBy]![dtstartDate4] DateTime,
[forms]![frmStrtDtStrBy]![dtstartDate4_2] DateTime;
SELECT ....
....BETWEEN [forms]![frmStrtDtStrBy]![dtstartDate4]
AND [forms]![frmStrtDtStrBy]![dtstartDate4_2]

then Access would know that form parameters were type Date.

Another posssibility is that using "BETWEEN" returns no records,
especially if your dates have a time component. To test,
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String, lngCnt As Long

stDocName = "fqryProSubCatDate"
Click to expand...
If DCount("*","fqryProSubCatDate")>0 then
DoCmd.OpenQuery stDocName, acNormal, acEdit
Click to expand...
Else
MsgBox "No Records!"
End If
Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
Click to expand...

If your date field does have time component,
it usually works to change "BETWEEN...AND.."
to

WHERE
[Datefield] >= [Forms]![frmName]![startdatetextbox]
AND
[Datefield] <= [Forms]![frmName]![enddatetextbox]


If that does not solve your error,
the next thing I might do is verify I am getting
from form exactly what I think I should.

With form open, go into VB Editor (CTRL-G
or ALT F11), and in bottom Immediate window,
find out what form is for sure returning:

?[forms]![frmStrtDtStrBy]![dtstartDate4]

?[forms]![frmStrtDtStrBy]![dtstartDate4_2]

?[forms]![frmStrtDtStrBy]![cmbProductName4]

?[forms]![frmStrtDtStrBy]![comboSubCat4]

then test your DatePart concatenations
(actually, I don't know why you are doing this...
couldn't you just use something like

Format([forms]![frmStrtDtStrBy]![dtstartDate4], "\#mm\/dd\/yyyy\#)

in your WHERE clause?)

Well...just some ideas...

Gary Walter

Matt wrote
I get "You cacelled the previous operation." error when I
try to run a query.

I have a form with two drop down boxes, a begin and a end
date and a button that runs a query. The query looks at
the form to determine what to display.

Here is the SQL for the query:

SELECT tblEvents.StartDate, tblEvents.ProductID,
tblEvents.SubCategorieID, tblEvents.AssignedToID,
tblEvents.EmployeeID, tblEvents.CompletedByDate,
tblEvents.CompletedByID, tblEvents.Discrepancy,
tblEvents.Resolution, tblEvents.VerifiedByID,
tblEvents.VerifiedByDate, tblEvents.PriorityID,
tblEvents.ProblemTypeID, tblEvents.VersionID,
tblEvents.EventID, tblEvents.Subject
FROM tblEvents
WHERE (((tblEvents.StartDate) Between DatePart("m",
[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" & DatePart
("d",[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" &
DatePart("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4])
And DatePart("m",[forms]![frmStrtDtStrBy]!
[dtstartDate4_2]) & "/" & DatePart("d",[forms]!
[frmStrtDtStrBy]![dtstartDate4_2]) & "/" & DatePart
("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4_2])) AND
((tblEvents.ProductID)=[forms]![frmStrtDtStrBy]!
[cmbProductName4]) AND ((tblEvents.SubCategorieID)=
[forms]![frmStrtDtStrBy]![comboSubCat4]))
ORDER BY tblEvents.StartDate, tblEvents.AssignedToID;

FYI: I have used the "Between DatePart" of the WHERE
clause in other queries and they work.

And here is the VB script for the button:


Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "fqryProSubCatDate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

Any ideas?

Thanks,

Matt
Click to expand...
 
M

mrs

Thanks Gary I'll take a look at # delimiters.

-----Original Message-----
Hi Matt,

A "date" typically needs to be delimited by "#"'s in a
query.
WHERE (((tblEvents.StartDate) Between "#" & DatePart ("m",
[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" & DatePart
("d",[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" &
DatePart("yyyy",[forms]![frmStrtDtStrBy]! [dtstartDate4]) & "#"
And "#" & DatePart("m",[forms]![frmStrtDtStrBy]!
[dtstartDate4_2]) & "/" & DatePart("d",[forms]!
[frmStrtDtStrBy]![dtstartDate4_2]) & "/" & DatePart
("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4_2]))
Click to expand...
& "#"

or set type in PARAMETERS clause

PARAMETERS [forms]![frmStrtDtStrBy]![dtstartDate4] DateTime,
[forms]![frmStrtDtStrBy]![dtstartDate4_2] DateTime;
SELECT ....
....BETWEEN [forms]![frmStrtDtStrBy]![dtstartDate4]
AND [forms]![frmStrtDtStrBy]![dtstartDate4_2]

then Access would know that form parameters were type Date.

Another posssibility is that using "BETWEEN" returns no records,
especially if your dates have a time component. To test,
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String, lngCnt As Long

stDocName = "fqryProSubCatDate"
Click to expand...
If DCount("*","fqryProSubCatDate")>0 then
DoCmd.OpenQuery stDocName, acNormal, acEdit
Click to expand...
Else
MsgBox "No Records!"
End If
Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
Click to expand...

If your date field does have time component,
it usually works to change "BETWEEN...AND.."
to

WHERE
[Datefield] >= [Forms]![frmName]![startdatetextbox]
AND
[Datefield] <= [Forms]![frmName]![enddatetextbox]


If that does not solve your error,
the next thing I might do is verify I am getting
from form exactly what I think I should.

With form open, go into VB Editor (CTRL-G
or ALT F11), and in bottom Immediate window,
find out what form is for sure returning:

?[forms]![frmStrtDtStrBy]![dtstartDate4]

?[forms]![frmStrtDtStrBy]![dtstartDate4_2]

?[forms]![frmStrtDtStrBy]![cmbProductName4]

?[forms]![frmStrtDtStrBy]![comboSubCat4]

then test your DatePart concatenations
(actually, I don't know why you are doing this...
couldn't you just use something like

Format([forms]![frmStrtDtStrBy]!
Click to expand...
[dtstartDate4], "\#mm\/dd\/yyyy\#)
in your WHERE clause?)

Well...just some ideas...

Gary Walter

Matt wrote
I get "You cacelled the previous operation." error when I
try to run a query.

I have a form with two drop down boxes, a begin and a end
date and a button that runs a query. The query looks at
the form to determine what to display.

Here is the SQL for the query:

SELECT tblEvents.StartDate, tblEvents.ProductID,
tblEvents.SubCategorieID, tblEvents.AssignedToID,
tblEvents.EmployeeID, tblEvents.CompletedByDate,
tblEvents.CompletedByID, tblEvents.Discrepancy,
tblEvents.Resolution, tblEvents.VerifiedByID,
tblEvents.VerifiedByDate, tblEvents.PriorityID,
tblEvents.ProblemTypeID, tblEvents.VersionID,
tblEvents.EventID, tblEvents.Subject
FROM tblEvents
WHERE (((tblEvents.StartDate) Between DatePart("m",
[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" & DatePart
("d",[forms]![frmStrtDtStrBy]![dtstartDate4]) & "/" &
DatePart("yyyy",[forms]![frmStrtDtStrBy]! [dtstartDate4])
And DatePart("m",[forms]![frmStrtDtStrBy]!
[dtstartDate4_2]) & "/" & DatePart("d",[forms]!
[frmStrtDtStrBy]![dtstartDate4_2]) & "/" & DatePart
("yyyy",[forms]![frmStrtDtStrBy]![dtstartDate4_2])) AND
((tblEvents.ProductID)=[forms]![frmStrtDtStrBy]!
[cmbProductName4]) AND ((tblEvents.SubCategorieID)=
[forms]![frmStrtDtStrBy]![comboSubCat4]))
ORDER BY tblEvents.StartDate, tblEvents.AssignedToID;

FYI: I have used the "Between DatePart" of the WHERE
clause in other queries and they work.

And here is the VB script for the button:


Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "fqryProSubCatDate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

Any ideas?

Thanks,

Matt
Click to expand...


.
Click to expand...
 
Top