×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

(OP)
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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close