Optional Parameters With Date Range in SP Did Not Return Any Records

[Lin100]

Optional Parameters With Date Range in
Stored Procedure Did Not Return Any Records.

Optional Parameters in Stored Procedure
Did Not Return Any Records.
Access 2002 (SP1) and SQL 2000 Server

I have an ADP form that have a combo box and three text
boxes. Two of the text boxes are use to hold dates
(From_Date and To_Date),and I wanted it to do the following listed below:


1) Whenever a user select a value from a combo box, but
leave the text box blank, then Access will retrieve
all of the records that meet the ONE criteria from
the combo box, and the two criteria from the date text boxes.

2) Whenever a user put in a value from a text box, but
leave the combo box blank, then Access will retrieve
all of the records that meet the ONE criteria from
the text box, and the two criteria from the date text boxes.

3) Whenever a user select a value from a combo box, and
enter a value in the text box, then Access will retrieve
all of the records that meet the TWO criteria - one from
the combo box, and the other from a text box, and the two criteria from the date text boxes.

Currently. Access did not retrieve any records
because of the two date fields. If the two date fields
are not in the Stored Procedure, everything
would work correctly.

Example:
From_Date: 1-1-2000
To_Date: 12-1-2002
cboParameter: 100
Text_Au_Ord: 2

////////////////////////////////////////

Form Name: frmParameters_4
Combo Box Name: cboParameter
Text Box Name: Text_Au_Ord
Where: These 2 controls are located on the Form Header Of
the Form frmParameters_4

Form Name: frmParameters_4
Record Source: byroyalty_3
Input Parameters:
@percentage int=Forms!frmParameters_4!cboParameter,
@auord int=Forms!frmParameters_4!Text_Au_Ord

THE TWO PARAMETERS ABOVE ARE OPTIONAL PARAMETERS.
IF NO parameter are given (combo box and text
box are blank), then all records will be
displayed on the form frmParameters_4.

Text Box: txtAuID
Where: On the Detail Section of The Form frmParameters_4

////////////////////////////////////////

CREATE PROCEDURE byroyalty_3 @From_Date Datetime, @To_Date Datetime, @percentage int, @auord int
AS
select au_id
from titleauthor
WHERE (@From_Date >= titleauthor.Date_Of_Title AND @To_Date <= titleauthor.Date_Of_Title)
AND
titleauthor.royaltyper = case
when @percentage is null then
titleauthor.royaltyper
else @percentage
end
AND titleauthor.au_ord = case
when @auord is null then
titleauthor.au_ord
else @auord
end
GO

/////////////////////////////////////////

Private Sub Form_Open(Cancel As Integer)
Me.From_Date = #1/1/2000#
Me.To_Date = Date
End Sub

Private Sub cboParameter_AfterUpdate()
Me.Requery
End Sub

Private Sub Text_Au_Ord_AfterUpdate()
Me.Requery
End Sub

 

[Lin100]

I got it solved. It is
YES
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date, and not

NO
WHERE (@From_Date >= titleauthor.Date_Of_Title AND @To_Date <= titleauthor.Date_Of_Title)