QueryByForm Approach

[David H]

If you were building a form implementing QueryByForm and could only provide a
single textbox to capture the input, how would you parse out the following
string ["John Smith" Peter ] so that the resultant WHERE statement could
becreated WHERE (NameFirst = "Peter") OR (NameFirst ="John" AND NAMELAST =
"Smith")?

Right now I've effectively implemented single value searches such as [John
Smith Peter] where all names with John, Smith or Peter in the first or last
name are returned. But, I would like to provide my users with a Google type
of search that considers "John Smith" as an exact match.

I'm already using the Split() function to break the string apart, but I
can't figure out how to detect and retrieve a value entered in quotes - other
than looping through the string checking each individual character's ASCII
code.

 

[Jack Leach]

I've done something similar parsing parentheses within string for a program
converter. It was quite a while ago, but if I remember correctly I used two
integer indicators to find the first and last position of the paren using
Instr(), and then the mid function to extract those between. I was able to
make this recursive for parens (read from the right side of the string
instead of the left... a backwards instr, if you will), but quotes may be a
little more difficult for that.

Once you have that string singled out, I would think you should be able to
coordinate it with your query without much of an issue.

If you'd like I can dig up the project and take a look for some more details.

I've also got a few functions that optionally add multiple where conditions
based on certain criteria (from search options on a form, but should work
here).

Complete air code, but something like this maybe

Function GetSQLString() as String
Dim iFirst As Integer 'position of the first "
Dim iNext As Integer 'Position of the next "
Dim sSegment as String 'portion within the quotes
iFirst = Instr(1, string, """")
iNext = Instr(iFirst, string, """")
sSegment = Mid(string, iFirst, iNext - iFirst)

sWhere = sWhere * " " & sSegment

End Function



hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[David H]

I'm going to have to play with it since I do want to give the users the
ability to enter strings such as [ "John Smith" "Peter Paul" "Mike Smith" ].
But Instr() should get me started.

I've done something similar parsing parentheses within string for a program
converter. It was quite a while ago, but if I remember correctly I used two
integer indicators to find the first and last position of the paren using
Instr(), and then the mid function to extract those between. I was able to
make this recursive for parens (read from the right side of the string
instead of the left... a backwards instr, if you will), but quotes may be a
little more difficult for that.

Once you have that string singled out, I would think you should be able to
coordinate it with your query without much of an issue.

If you'd like I can dig up the project and take a look for some more details.

I've also got a few functions that optionally add multiple where conditions
based on certain criteria (from search options on a form, but should work
here).

Complete air code, but something like this maybe

Function GetSQLString() as String
Dim iFirst As Integer 'position of the first "
Dim iNext As Integer 'Position of the next "
Dim sSegment as String 'portion within the quotes
iFirst = Instr(1, string, """")
iNext = Instr(iFirst, string, """")
sSegment = Mid(string, iFirst, iNext - iFirst)

sWhere = sWhere * " " & sSegment

End Function



hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If you were building a form implementing QueryByForm and could only provide a
single textbox to capture the input, how would you parse out the following
string ["John Smith" Peter ] so that the resultant WHERE statement could
becreated WHERE (NameFirst = "Peter") OR (NameFirst ="John" AND NAMELAST =
"Smith")?

Right now I've effectively implemented single value searches such as [John
Smith Peter] where all names with John, Smith or Peter in the first or last
name are returned. But, I would like to provide my users with a Google type
of search that considers "John Smith" as an exact match.

I'm already using the Split() function to break the string apart, but I
can't figure out how to detect and retrieve a value entered in quotes - other
than looping through the string checking each individual character's ASCII
code.

 

[David H]

Here's the initial solution which was frightenling easy to come up with. It
allows for inputs such as [test "john wilson" john "sleep" mary wilson "bed
frame"] to be entered with anything in quotes to be extracted as a whole. It
does need some additional code to deal with situations where there are no
apostrophes in the string at all such as [test john wilson] but that should
be a simple if...then and probably code to check if there's an uneven number
of apostrophes such as [test "john wilson]. The code assumes that there are
spaces between the individual values otherwise [test"john wilson"mary] will
result in [john wilson] and [testmary].

Its a start.

Sub test(strTest As String)

While InStr(1, strTest, Chr(34)) > 0
'get the position of the first apostrophe
firstPosition = InStr(1, strTest, Chr(34))
'get the next, +1 is neccessary due to the presence of the apostrophe
nextPosition = InStr(firstPosition + 1, strTest, Chr(34))
'extract the string within the apostrophes along with the apostrophes
string1 = Mid(strTest, firstPosition, nextPosition - firstPosition + 1)
'remove the string from the original
strTest = Replace(strTest, string1, "")
'replace any double spaces with a single space
strTest = Replace(strTest, " ", " ")
'strip the apostrophes from the string that we extracted
string1 = Replace(string1, Chr(34), "")
Debug.Print string1
Debug.Print strTest
Wend

End Sub

I'm going to have to play with it since I do want to give the users the
ability to enter strings such as [ "John Smith" "Peter Paul" "Mike Smith" ].
But Instr() should get me started.

I've done something similar parsing parentheses within string for a program
converter. It was quite a while ago, but if I remember correctly I used two
integer indicators to find the first and last position of the paren using
Instr(), and then the mid function to extract those between. I was able to
make this recursive for parens (read from the right side of the string
instead of the left... a backwards instr, if you will), but quotes may be a
little more difficult for that.

Once you have that string singled out, I would think you should be able to
coordinate it with your query without much of an issue.

If you'd like I can dig up the project and take a look for some more details.

I've also got a few functions that optionally add multiple where conditions
based on certain criteria (from search options on a form, but should work
here).

Complete air code, but something like this maybe

Function GetSQLString() as String
Dim iFirst As Integer 'position of the first "
Dim iNext As Integer 'Position of the next "
Dim sSegment as String 'portion within the quotes
iFirst = Instr(1, string, """")
iNext = Instr(iFirst, string, """")
sSegment = Mid(string, iFirst, iNext - iFirst)

sWhere = sWhere * " " & sSegment

End Function



hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If you were building a form implementing QueryByForm and could only provide a
single textbox to capture the input, how would you parse out the following
string ["John Smith" Peter ] so that the resultant WHERE statement could
becreated WHERE (NameFirst = "Peter") OR (NameFirst ="John" AND NAMELAST =
"Smith")?

Right now I've effectively implemented single value searches such as [John
Smith Peter] where all names with John, Smith or Peter in the first or last
name are returned. But, I would like to provide my users with a Google type
of search that considers "John Smith" as an exact match.

I'm already using the Split() function to break the string apart, but I
can't figure out how to detect and retrieve a value entered in quotes - other
than looping through the string checking each individual character's ASCII
code.