Optional Parameters in SP Did Not Return Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Optional Parameters in SP Did Not Return Records

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2008-03-18 : 13:56:05

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

I have an Access ADP form that have a combo box and a text box, and I wanted it to do the following:

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 of Error:
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 whether or not the combo box
value is a 100.

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources