QueryByForm Approach

  • Thread starter Thread starter David H
  • Start date Start date
D

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.
 
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)
 
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.

Jack Leach said:
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 said:
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.
Click to expand...
Click to expand...
 
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

David H said:
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.

Jack Leach said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top