Case sensitive LIKE '*text*' queries?

[joebater]

Hi there

Ok so the obvious way to search on a term in freefrom text records might look something _like_:

SELECT fldDate, fldQuestion, fldAnswer
FROM tblQuestion
WHERE fldQuestion LIKE '*[txtKeyword.text]*'

But what exactly does that return, if I search for the term 'AT' I'll also get 'cat', 'sat', 'match', 'At the ...' etc.

All of the case sensitive keyword threads I've seen so far seem to concentrate on searching for the entire keyword using functions in a module headed OPTION COMPARE BINARY. This seems fair but I don't see how to apply (and further filter using such a function) what is returned by the base LIKE clause from the SQL.

I would be grateful to correspond with anyone else who has dealt with something like this?

Many thanks for reading/answering.

blue skies
j.

 

[glynch]

It isn't entirely clear to me why using OPTION COMPARE BINARY doesn't meet your needs.

I have used it to find the first capialized letter in a string such as:

txtFieldA
lblLabelB

I wrote code to find the first capital letter, and then I grab all of the characters previous to that capital letter and determine the data type based on the prefix.

I can find the "F" and then grab the "txt" with the first one and then the "L" and grab the "lbl".

This was for a specific cucumstance where all of the field names were prefixed with lower case characters. I can give you more specific code on Tuesday if you are interested.

HTH

 

[joebater]

Hi Glyph (and other readers)

Please allow me to give some context which will illuminate my problem (which is probably just ignorance on my part).

I have a form (largely based on two tables) which has RFI's that have been sent to the companies regarding components that the engineering company I work for are interested in purchasing. The two tables are (sanitised) tblQuestion, tblResponse (the split is that the same question is sent, potentially, to a number of manufacturers about their product.

The idea is that on a form a "keyword" can be entered and all questions and responses containing this term can be brought up.
In the form module I now have:

Option Compare Database
Option Explicit

' other
' code
' goes
' here

Private Sub cmdQuery_Click()
'error check stuff goes here

Dim strSelect As String, strFrom As String, strWhere As String
Dim strSQL As String

txtKeyword.SetFocus

strSelect = "SELECT tblQuestion.QuestionID, tblQuestion.Number,
tblQuestion.Question, tblQuestion.Date, tblResponse.ResponseID,
tblResponse.Manufactuer, tblResponse.Response "

strFrom = " FROM tblQuestion INNER JOIN tblResponse ON
tblQuestion.QuestionID = tblResponse.QuestionID "

strWhere = " WHERE tblQuestion LIKE '*" & txtKeyword.Text & "*' "

strSQL = strSelect & strFrom & strWhere

Me.RecordSource = strSQL

RefreshDatabaseWindow

'error trapping and exit sub

I guess that the SQL generated by the form should go into a QueryDef and then have the form take that as it's source.

However I'm still somewhat lost as how to go about the next part which is to add a checkbox "chkCaseSensitive" which enforces that the query result permits a case sensistive keywork search, so if they ask for "AT" they won't get every "cat" "that" sat" on the "mat".

Unless ...

There is a separate VBA module that generates the source for a QueryDef (having OPTION COMPARE BINARY rather than OPTION COMPARE DATABASE) which is used when the chkCaseSensitive box is checked. Would this mean a separate Qdf also? (the second having these options - can a qdf have these?)

But ...

I'm still lost has to how this is applied to the results of a SELECT FROM WHERE LIKE SQL statement?

Would be grateful to hear from anyone who can help/sympathise.

blue skies
j.

 

[glynch]

There is problem with this statement:

strWhere = " WHERE tblQuestion LIKE '*" & txtKeyword.Text & "*' "

You need to designate what field in tblQuestion you are searching in. Like:

strWhere = " WHERE tblQuestion.Question LIKE '*" & txtKeyword.Text & "*' "

I would have a named query for your SQL statement and only add the WHERE clause in the code.

Here is a chunk of my code, (notice the vbBinaryCompare argument), qryAlphaNumeric returns a list of 26 capital letters:

'We loop thru the list of objects from tblObjects
rstObjects.MoveFirst
Do Until rstObjects.EOF
strObjectName = rstObjects!ObjectName
intPosition = 0

'This loop cycles through each character in the field name until it hits an upper case
'letter and then takes all of the characters previous to that point (the prefix)
Set rstAlphaNumeric = dbsCurrent.OpenRecordset("qryAlphaNumeric", dbOpenSnapshot)
intCharPos = 0
booSwitch = False

Do While Not booSwitch
intCharPos = intCharPos + 1
rstAlphaNumeric.MoveFirst
Do Until rstAlphaNumeric.EOF
strLetter = rstAlphaNumeric!Character
intPosition = InStr(1, Mid(strObjectName, intCharPos, 1), strLetter, vbBinaryCompare)
If intPosition > 0 Then
intPosition = InStr(1, strObjectName, strLetter, vbBinaryCompare)
booSwitch = True
strPrefix = Left(strObjectName, intPosition - 1)
Exit Do
End If
rstAlphaNumeric.MoveNext
Loop
Loop

If Len(Trim(strPrefix)) = 0 Then
Beep
MsgBox "The application object '" & strObjectName & "' needs to have a prefix."
booAbortProcess = True

HTH

 

[joebater]

hi glynch

let me try to understand your solution at a higher conceptual level. you've opened a recordset and have nested do loops, firstly iterating through the recordset, then within each record examining that record using an instr function to get the capital letter (obtained from qryAlphaNumeric).

If my understanding is correct then I should, firstly use a make-table query (non-case sensitive) to capture all matches of the text. Then if the case-sensitive checkbox is checked, perform a nested loop search in the manner you suggest (which eliminates as much combinatorial complexity as possible).

The InStr fuction with vbBinaryCompare argument is invaluable - thanks. Would the OPTION COMPARE BINARY option have be set at the module level, or does OPTION COMPARE DATABASE continue to be used? (I'll guess I'll be trying that anyway).

many thanks again

blue skies
j.

 

[glynch]

I probably sent you too much code, I just thought you might be interested in it if you find yourself in a similar situation (God knows why!). Yours is a little different but still a little tricky. I would create a recordset out of your generic select statement (rstQuestion), step thru and evaluate each one like this:

sText = txtKeyword.Text
sQuestion = rstQuestion!Question

If InStr(sQuestion, sText , vbBinaryCompare)

Then maybe take the QuestionID values and save them to a temp table and then capture the set of records you need by querying your QuestionID values where they match the values in the temp table. You would need to either recreate the temp table each time with a make-table (and delete the temp table when done), or use an append query and delete all recs at end of processing.

This seems awfully complicated, I'm sure you can get it to work, but I'm not sure it is the most efficient way of accomplishing this task.

Good luck