Wild Card In SQL String

[Guest]

I am using a variable in an SQL string called "strName" to apply a filter to
return a specific name.

ie
Dim strName as string
strName = "Jeff"

SELECT tblName.Name
FROM tblName
WHERE (((tblName.Name)=""" & strName & """));

What can I set this variable ("strName") to so that it will return ALL NAMES
in the table, tblName (not just Jeff)?

It seems simple but I am stuck on this one.

Many Thanks

Ross

 

[mcescher]

Dim strSQL As String

strSQL = "SELECT tblName.[Name] FROM tblName"
If Len(strName) > 0 Then
strSQL = strSQL & " WHERE tblName.[Name] = """ & strName & """"
End If
strSQL = strSQL & ";"

Dim strSQL As String, strWhere As String
strWhere = "WHERE "
strSQL = "SELECT tblName.[Name] FROM tblName"
If Len(strField1)>0 Then _
strWhere = strWhere & "tblName.Field1 = """ & strField1 & """ AND "
If Len(strField2)>0 Then _
strWhere = strWhere & "tblName.Field2 = """ & strField2 & """ AND "
If Len(strField3)>0 Then _
strWhere = strWhere & "tblName.Field3 = """ & strField3 & """ AND "
If Len(strField4)>0 Then _
strWhere = strWhere & "tblName.Field4 = """ & strField4 & """ AND "
If Len(strField5)>0 Then _
strWhere = strWhere & "tblName.Field5 = """ & strField5 & """
If Right(strWhere,4) = " AND" then strWhere =
Left(strWhere,Len(strWhere)-4)
If Len(strWhere) > 6 Then strSQL = strSQL & strWhere
strSQL = strSQL & ";"

Klatuu,

I have never considered inserting an if / then statement as you are building
the string. That is very powerful. Thank YOU!

However there is more to the story. I acutally have a string with 5 fields
and the client wants to select filters for any or all of the five fields
simultaneously. This could be about 5*5 = 25 combinations. (ie filters on
Fields 1,3,5 or Field 2 (only) or Field 1,2,3,4 and 5)

The Ideal solution for this problem, it seems to me, is if I could set the
variable ("strName") for each field equals to ' "Like "*" ' if you want no
filter on that field. Unfortunately, I have not been able to find syntax
that will work to return all of the records for a given field.

I hope I have explained this clearly.

Do you have any other suggestions?

Thank you

Ross

If this is SQL you are building and using in VBA, I usually use something
like this:

Dim strSQL As String

strSQL = "SELECT tblName.[Name] FROM tblName"
If Len(strName) > 0 Then
strSQL = strSQL & " WHERE tblName.[Name] = """ & strName & """"
End If
strSQL = strSQL & ";"

I am using a variable in an SQL string called "strName" to apply a filter to
return a specific name.

ie
Dim strName as string
strName = "Jeff"

SELECT tblName.Name
FROM tblName
WHERE (((tblName.Name)=""" & strName & """));

What can I set this variable ("strName") to so that it will return ALL NAMES
in the table, tblName (not just Jeff)?

It seems simple but I am stuck on this one.

Many Thanks

Ross

 

[mcescher]

Glad to help.

Chris

This is Awsome,

Thank You!

Dim strSQL As String
strSQL = "SELECT tblName.[Name] FROM tblName"
If Len(strName) > 0 Then
strSQL = strSQL & " WHERE tblName.[Name] = """ & strName & """"
End If
strSQL = strSQL & ";"

Dim strSQL As String, strWhere As String
strWhere = "WHERE "
strSQL = "SELECT tblName.[Name] FROM tblName"
If Len(strField1)>0 Then _
strWhere = strWhere & "tblName.Field1 = """ & strField1 & """ AND "
If Len(strField2)>0 Then _
strWhere = strWhere & "tblName.Field2 = """ & strField2 & """ AND "
If Len(strField3)>0 Then _
strWhere = strWhere & "tblName.Field3 = """ & strField3 & """ AND "
If Len(strField4)>0 Then _
strWhere = strWhere & "tblName.Field4 = """ & strField4 & """ AND "
If Len(strField5)>0 Then _
strWhere = strWhere & "tblName.Field5 = """ & strField5 & """
If Right(strWhere,4) = " AND" then strWhere =
Left(strWhere,Len(strWhere)-4)
If Len(strWhere) > 6 Then strSQL = strSQL & strWhere
strSQL = strSQL & ";"

Klatuu,
I have never considered inserting an if / then statement as you are building
the string. That is very powerful. Thank YOU!
However there is more to the story. I acutally have a string with 5 fields
and the client wants to select filters for any or all of the five fields
simultaneously. This could be about 5*5 = 25 combinations. (ie filters on
Fields 1,3,5 or Field 2 (only) or Field 1,2,3,4 and 5)
The Ideal solution for this problem, it seems to me, is if I could set the
variable ("strName") for each field equals to ' "Like "*" ' if you want no
filter on that field. Unfortunately, I have not been able to find syntax
that will work to return all of the records for a given field.
I hope I have explained this clearly.
Do you have any other suggestions?
Thank you
Ross
:
If this is SQL you are building and using in VBA, I usually use something
like this:
Dim strSQL As String
strSQL = "SELECT tblName.[Name] FROM tblName"
If Len(strName) > 0 Then
strSQL = strSQL & " WHERE tblName.[Name] = """ & strName & """"
End If
strSQL = strSQL & ";"
:
I am using a variable in an SQL string called "strName" to apply a filter to
return a specific name.
ie
Dim strName as string
strName = "Jeff"
SELECT tblName.Name
FROM tblName
WHERE (((tblName.Name)=""" & strName & """));
What can I set this variable ("strName") to so that it will return ALL NAMES
in the table, tblName (not just Jeff)?
It seems simple but I am stuck on this one.
Many Thanks
Ross- Hide quoted text -- Show quoted text -