search criteria as unenclosed text

[knownote]

Hello All,

An unbound search form searches up to 5 separate criteria for selected table or query by using criteria enclosed in asterisks - *criteria*

How can code be changed so criteria can be entered without being enclosed in asterisks?

From sections below, not sure how to edit syntax for
1. strSQL = "Select * "
2. "Select * from [" & Me.lstTables.Column(0) & "] Where 1=2", dbOpenSnapshot)
3. strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "] like '*'"


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'
On Error GoTo ErrHandler
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim i As Integer
Dim j As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim qdf As DAO.QueryDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
...


'Get the a reference to the field in the table/Query as
'we'll need it for BuildCriteria later on
If Me.lstTables.Column(1) = "Table" Then
Set tdf = db.TableDefs(Me.lstTables.Column(0))
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & Me.lstTables.Column(0) & "] Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType & Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "] like '*'"
End If

End If
Next
'The final all important SQL statement
strSQL = strSQL & " from [" & Me.lstTables & "] Where " & strWhere

'If the user has modified the SQL directly, take what they've typed in
If Nz(Me.chkEditSQL, False) = False Then
'"save" it in the textbox
Me.txtSQL = strSQL
End If
...

 

[genomon]

I don't see any criteria enclosed in asterisks in the above code. An easy way would be to use Replace("criteria","*","").

Cogito eggo sum – I think, therefore I am a waffle.

 

[knownote]

What above code can be changed to type search string without enclosing it in asterisks?

I'm not sure how to edit syntax for whichever of these lines with an asterisk and/or Like operator will correct.

1. strSQL = "Select * "
2. "Select * from [" & Me.lstTables.Column(0) & "] Where 1=2", dbOpenSnapshot)
3. strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "] like '*'

 

[joelflorendo]

Replace the like '* with just ' if you want your results to return based on an exact match.

 

[genomon]

like '*'

Not to mention that * is the wildcard, so you are basically saying "like everything".

Cogito eggo sum – I think, therefore I am a waffle.

 

[knownote]

How do you change having to enclose search string in asterisks *search string*

...
Else
strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "] like '*'"
End If
...

to not having to enclose search string?
2 unrelated examples:

parameter query
Like "*" & [Enter Search String for Last Name] & "*"

http://uhelpme.com

type any portion of: tek-tips thread
(male)

 

[joelflorendo]

The * is a wildcard search. It represents 0 or more characters. So a search on "like appl*" will return matches like "apple" and "application". You wouldn't really enclose anything with * unless you want to allow for any number of characters before and after the criteria string (so *appl* will return something like "grapple" in addition to the others above).

I'm guessing what you're looking for, instead of doing a wildcard search, is to do an exact match search. You need to replace the "like" with "=" and remove the "*".

So if you wanted to find all records that matched "appl" exactly, you would use:

WHERE [myFieldName]='appl'

If it's a numeric value your search for, you need to remove the single quotes.

With all that being said, I'm not sure I understand your code. I don't see where you're appending your search string at the end of the strWhere variable. But maybe that gets handled in the portion of the code you omitted(?)

 

[knownote]

I do want to allow for any number of characters before and after the text criteria string.
Now, both enclosed asterisks are needed to display text or memo field match results.

This works fine as is, just trying to simplify using db for others only if it's simple enough to be worth the effort.

strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "] like '*'"

Can Like be modified similar to this unrelated parameter query?

Like "*" & [Enter Search String for Last Name] & "*"

Matching last names display for any portion typed (start, middle, or end), without typing asterisks.

For example, ack results in Lastnames
Ackroyd, Jackson, Mackay, Slingback



---- most of relevant code, from start to listbox

Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'
On Error GoTo ErrHandler
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim i As Integer
Dim j As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim qdf As DAO.QueryDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second combo/textbox set
'so if there's only one criteria specified, don't need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If
'Get the a reference to the field in the table/Query as
'we'll need it for BuildCriteria later on
If Me.lstTables.Column(1) = "Table" Then
Set tdf = db.TableDefs(Me.lstTables.Column(0))
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & Me.lstTables.Column(0) & "] Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType & Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "] like '*'"
End If

End If
Next
'The final all important SQL statement
strSQL = strSQL & " from [" & Me.lstTables & "] Where " & strWhere

'If the user has modified the SQL directly, take what they've typed in
If Nz(Me.chkEditSQL, False) = False Then
'"save" it in the textbox
Me.txtSQL = strSQL
End If

 

[joelflorendo]

you mean:

strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "]  like '*" & Me("txtVal" & i) & "*'"

???
This still doesn't make sense to me though. At this point in your code, this line will only be run if Me("txtVal" & i) is null. I don't understand how you want to modify so that the user doesn't have to type in the * in their search criteria since this line is only run if the user didn't type anything in the search criteria. Or am I not interpreting your question correctly...?

 

[knownote]

Thanks for your help, since I'm not a programmer. I'll close this thread now, since this line is only run if the user didn't type anything in the search criteria. If I recognized that, I wouldn't have started this thread.