Optional Paramter

[Gene Ariani]

Is there an elegant way to have an optional parameter in sql string like

Select * from Name where (mytextbox is empty select everyone else select
lastname=mytextbox)
Any direction is appreciated it.
Thanks
Gene

[sg]

Assuming you're embedding this in a string, you could say:
sSQL = "Select * from Name " & _
iif(MyTextBox.Text = "", _
"Where Name like '" & MyTextBox.Text & "%'")

-----Original Message-----
Is there an elegant way to have an optional parameter in sql string like
Select * from Name where (mytextbox is empty select everyone else selectlastname=mytextbox)
Any direction is appreciated it.
Thanks
Gene
.

[Jay B. Harlow [MVP - Outlook]]

Gene,
This sounds more like a SQL Server or Access question.

You may want to ask this "down the hall" in an SQL Server, Access or the
ado.net newsgroup.

Hope this helps
Jay

"Gene Ariani" <ka*****@comcast.net> wrote in message
news:1P********************@comcast.com...

Is there an elegant way to have an optional parameter in sql string like

Select * from Name where (mytextbox is empty select everyone else select
lastname=mytextbox)
Any direction is appreciated it.
Thanks
Gene

[Gene Ariani]

Sg;

Thank you very much for taking the time to response. But I can’t seem to
concatenate your string correctly to make it work.
Thanks
Gene
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor]

Hi Gene,

Select * from Name where (mytextbox is empty select everyone else select
lastname=mytextbox)

What is the benefit of that above
If mytexbox.text ="" then
selectstring = "select * from name"
else
selectstring = "select * from name where lastname"' _
trim(mytextbox.text) & "'"
end if

Or do you want some more processing on your database computer?

I hope this helps a little bit?

Cor

[Gene Ariani]

Well I was trying to avoid writing multiple select statement and fit
everthing into one clean sql statement

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Cor]

Hi Gene,

I find this a very clean statement and when it is build it is only one thru
step in your computer tested by one digit.

When you put it in a SQL it has to be everytime evaluated and compiled on
your SQL server side.

And this statement shows direct what you are doing.

My choise would be easy.

Cor

[Jay B. Harlow [MVP - Outlook]]

Gene,
Then use a variation of Cor's example:

selectstring = "select * from name"

If mytexbox.text <> "" then
selectstring &= " where lastname""' _
trim(mytextbox.text) & """
end if

As Cor stated his original and my modified version are both very easy.

Hope this helps
Jay

"Gene Ariani" <ka*****@comcast.net> wrote in message
news:O9**************@tk2msftngp13.phx.gbl...

Well I was trying to avoid writing multiple select statement and fit
everthing into one clean sql statement

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Gene Ariani]

Thanks everyone for responding.
I basically followed the suggested solutions.

"Jay B. Harlow [MVP - Outlook]" <Ja********@email.msn.com> wrote in message
news:uz**************@TK2MSFTNGP09.phx.gbl...

Gene,
Then use a variation of Cor's example:

selectstring = "select * from name"

If mytexbox.text <> "" then
selectstring &= " where lastname""' _
trim(mytextbox.text) & """
end if

As Cor stated his original and my modified version are both very easy.

Hope this helps
Jay

"Gene Ariani" <ka*****@comcast.net> wrote in message
news:O9**************@tk2msftngp13.phx.gbl...

Well I was trying to avoid writing multiple select statement and fit
everthing into one clean sql statement

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!