Pass an Integer Parameter to SP 1

[sk1hotpepr]

Can anyone tell me how to pass a "less than" integer parameter to a stored procedure?

Set parOpt = cmd.CreateParameter("COption", adSmallInt, adParamInput, 4, cid)

This works for an integer, but when I try to pass the "<" symbol I get an error. Is there any way to do that?

 

[traingamer]

If the parameter is defined as an integer, you have to pass it as an integer.

You can change it to a string and change the SP accordingly.

traingamer

 

[dhookom]

Are you sure your parameter would include "<"? If the type of parameter is integer then you should not be able to include any characters other then integers.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sk1hotpepr]

What I'm trying to do is let a user choose what specific report to run or to choose to run them all. I'm just not sure how to translate that into a stored procedure to accept all values in the same parameter as a single number. i'm going to just try a case statement in the stored procedure unless there's a better way to do it.

 

[PHV]

Perhaps a 0 (zero) or a negative value for All ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

How about min and max values to describe a range?
We don't know what your store procedure is or what you are attempting to accomplish. By "specific report to run or run them all" it sounds like you have many similar reports. I doubt that is the case but I am not sure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sk1hotpepr]

Sorry,
I have an Access Form with an option box values 1-7 for individual contractor or value 8 to run a report for all contractors. The number is passed to a stored procedure which then opens the report for viewing.

 

[sk1hotpepr]

Here's an update of my problem -
This is how my parameter is created:

Set parOpt = cmd.CreateParameter("COption", adSmallInt, adParamInput, , cid)

This is part of my stored procedure:

WHERE     (dbo.ChangeOrderTable.ProjectNumber = @ProjectNumber) AND (dbo.ChangeOrderTable.ContractorID = (Case WHEN @COption < 8 THEN @COption ELSE '<8' END))

I get an error (obviously) because my data type is a small integer. How do I get the less than to go in to my criteria? I tried sending it as a string also. I know I'm missing something.

 

[PHV]

WHERE (dbo.ChangeOrderTable.ProjectNumber = @ProjectNumber) AND (dbo.ChangeOrderTable.ContractorID = @COption OR @COption >= 8)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLSister]

What I would do is not send a value if they choose all on the form. Make sure the parameter is defined to allow nulls. Then this type of construction would work

Where (@field1 is null or deptid = @field1)

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sk1hotpepr]

PHV,

That worked beautifully, thank you so much for the help! And thank you to all that responded, I appreciate your help also. I may finish this project some day~