Using the InputParameters on a form to pass user data to a SQLServer Stored Proc

[belangerg]

I am trying to pass parameters to a stored proc via the
InputParameters on the form.

I set up the Proc with a couple of parameters with the LIKE operator (e.g. LIKE
@p_File_Type)

On the form, in the InputParameters property I can string the values
out with commas inbetween and it works like a charm.

Problem is, if the user leaves a value blank, I would like the query
to select all values for that field. I have tried using the wildcard '%', leaving the
value blank, NULL, etc and nothing seems to work.

Anyone have any ideas?

[Rabbit]

The wildcard for any number of characters is "*"

[ADezii]

I am trying to pass parameters to a stored proc via the
InputParameters on the form.

I set up the Proc with a couple of parameters with the LIKE operator (e.g. LIKE
@p_File_Type)

On the form, in the InputParameters property I can string the values
out with commas inbetween and it works like a charm.

Problem is, if the user leaves a value blank, I would like the query
to select all values for that field. I have tried using the wildcard '%', leaving the
value blank, NULL, etc and nothing seems to work.

Anyone have any ideas?

This should work but has not been thoroughly tested. You must modify the Stored Procedure directly so that it returns the specified value or ALL (%). Again, it has not been thoroughly tested in the InputParameters context, but it will return specified values and ALL Records if % is entered as a Criteria. Let me know how you make out.
[CODE=sql]ALTER PROCEDURE dbo.StoredProce dure1
@strCity varchar(30)
AS SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy , BirthDate, HireDate, Address, City, Region,
PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo
FROM dbo.Employees
WHERE City Like '%' + @strCity + '%'[/CODE]

[ADezii]

The wildcard for any number of characters is "*"

Sorry Rabbit:
We are in the world of SQL ServerLand where "*" and "?" don't exist anymore, and "%" and "_" are the Status Quo (Access Projects). The InputParameters Property is only applicable to Access Projects, and does not even exist in conventional Access Databases. LOL.

[Rabbit]

SQL server? I should pay more attention, shouldn't this be in the SQL server forum then?

[ADezii]

SQL server? I should pay more attention, shouldn't this be in the SQL server forum then?

Not really, because it's a Project, it's an Access Front End connecting to an SQL Server Back End, and besides InputParameters is a Form Property relating to the Access Component.

[Rabbit]

But in the end, what they need is correct SQL server syntax and not Access syntax.

[ADezii]

But in the end, what they need is correct SQL server syntax and not Access syntax.

You are correctamundo.