Optional Parameters in Stored Procedure Did Not Return Any Records 1

[Lin100]

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 a text box,
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.

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.

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.

Currently. Access only retrieve records when I select
a value from a combo box, and ignore the criteria
of the text box value.

Example:
combo box value: 100
text box value: 1
Result: Retrieve all records where the combo box
value is 100 including records where the
text box value is 1, 2, 3 or otherwise.

combo box value: 100
text box value: BLANK
Result: Retrieve all records in the table,
regardless of the two specify criteria above

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

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

Form Name: frmParameters_2
Record Source: byroyalty_2
Input Parameters:
@percentage int=Forms!frmParameters_2!cboParameter,
@auord int=Forms!frmParameters_2!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_2.

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

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

CREATE PROCEDURE byroyalty_2 @percentage int, @auord int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage OR
@percentage IS NULL
AND titleauthor.au_ord = @auord OR
@auord IS NULL
GO

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

Private Sub cboParameter_AfterUpdate()
Me.Requery
End Sub

Private Sub Text_Au_Ord_AfterUpdate()
Me.Requery
End Sub

 

[PWise]

try

select au_id 
from titleauthor
where 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

 

[Lin100]

Thank you pwise for the solution.

 

[Lin100]

Hi Pwise.

I have another question that is identical to this one except
that included two date fields on an ADP form, namely
From_Date and To_Date. Since you have worked on it before
I thought you could solve this one easily.

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

 

[PWise]

Select 
select au_id 
from titleauthor
where 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
[b]and [i]datefield[/i] between [i]@fromdatepram[/i] and [i]@enddatepram[/i][/b]