VBA function break string input for SQL string

[Phil Latio]

I'm kind of looking for some guidance in respect of this obstacle I'm
up against - please let me expand;

I have an option group with three options (AND, OR, & NONE) and a text
box which will accept free text - the text will become criteria in a
SQL string and depending upon the option the WHERE clause will be
AND/OR LIKE textCriteria1 AND/OR LIKE textCriteria2....

I'm trying to create a VBA function to take the free text and break up
the component words and return them back for use in the WHERE
clause.... and I'm stuck at this point!

I'm quite looking more guidance and pointers on this, rather than some
kindly person providing an elegant VBA code script.
I know what I'm doing after I've got the SQL string built, it's just
breaking the component words into an AND/OR string for the WHERE
clause.

As I say, any guidance would be appreciated.

Thanks in advance

Phil

[chris.nebinger]

Well, you have to consider precedence:

Where (State = "FL" or State="AL") And SalesGuy = "JIM"

is different from:

Where State = "FL" or State="AL" And SalesGuy = "JIM"
I would almost think you'd be better off leaving a text box and let
them freeform type the where clause.
Chris Nebinger
Phil Latio wrote:

I'm kind of looking for some guidance in respect of this obstacle I'm
up against - please let me expand;

I have an option group with three options (AND, OR, & NONE) and a text
box which will accept free text - the text will become criteria in a
SQL string and depending upon the option the WHERE clause will be
AND/OR LIKE textCriteria1 AND/OR LIKE textCriteria2....

I'm trying to create a VBA function to take the free text and break up
the component words and return them back for use in the WHERE
clause.... and I'm stuck at this point!

I'm quite looking more guidance and pointers on this, rather than some
kindly person providing an elegant VBA code script.
I know what I'm doing after I've got the SQL string built, it's just
breaking the component words into an AND/OR string for the WHERE
clause.

As I say, any guidance would be appreciated.

Thanks in advance

Phil

[Phil Latio]

Hi Chris
Thanks for the quick response - good point about the precedence.
It's intended the criteria be applied to a single memo field, so I need
to think this through little.

Cheers

Phil

ch************@gmail.com wrote:

Well, you have to consider precedence:

Where (State = "FL" or State="AL") And SalesGuy = "JIM"

is different from:

Where State = "FL" or State="AL" And SalesGuy = "JIM"
I would almost think you'd be better off leaving a text box and let
them freeform type the where clause.
Chris Nebinger
Phil Latio wrote:

I'm kind of looking for some guidance in respect of this obstacle I'm
up against - please let me expand;

I have an option group with three options (AND, OR, & NONE) and a text
box which will accept free text - the text will become criteria in a
SQL string and depending upon the option the WHERE clause will be
AND/OR LIKE textCriteria1 AND/OR LIKE textCriteria2....

I'm trying to create a VBA function to take the free text and break up
the component words and return them back for use in the WHERE
clause.... and I'm stuck at this point!

I'm quite looking more guidance and pointers on this, rather than some
kindly person providing an elegant VBA code script.
I know what I'm doing after I've got the SQL string built, it's just
breaking the component words into an AND/OR string for the WHERE
clause.

As I say, any guidance would be appreciated.

Thanks in advance

Phil

[Bob Quintal]

"Phil Latio" <ph********@lycos.co.ukwrote in
news:11*********************@m7g2000cwm.googlegrou ps.com:

Hi Chris
Thanks for the quick response - good point about the
precedence. It's intended the criteria be applied to a single
memo field, so I need to think this through little.

Cheers

Phil

I built a text search similar to what you want. Had 4 textboxes
one above the other labeled "enter keyword to find" with
3comboboxes containing blank; and;or;and not; between
the textboxes. all except the first textbox were not visible.

The first textbox's afterupdate event set the first combo's
..visible to true, the first combo's after update set the second
textbox's .visible to true, and so on. If any comboboxes had
differing connectives, A little modal form would pop up and
allow the user to option

(x and y) or z
x and (y or z)

created from "(x " & cbo1 &" y) " & cbo2 & " z"
"x " & cbo1 &" (y " & cbo2 & " z)" for three variables,

(W or x) and (y or z)
W or (x and y) or z
or
(W or x or y) and z
W or x or (y and z)

then some additional code built the Where clause.

Hope this gives you some ideas.

Q

ch************@gmail.com wrote:

>Well, you have to consider precedence:

Where (State = "FL" or State="AL") And SalesGuy = "JIM"

is different from:

Where State = "FL" or State="AL" And SalesGuy = "JIM"
I would almost think you'd be better off leaving a text box
and let them freeform type the where clause.
Chris Nebinger
Phil Latio wrote:

I'm kind of looking for some guidance in respect of this
obstacle I'm up against - please let me expand;

I have an option group with three options (AND, OR, & NONE)
and a text box which will accept free text - the text will
become criteria in a SQL string and depending upon the
option the WHERE clause will be AND/OR LIKE textCriteria1
AND/OR LIKE textCriteria2....

I'm trying to create a VBA function to take the free text
and break up the component words and return them back for
use in the WHERE clause.... and I'm stuck at this point!

I'm quite looking more guidance and pointers on this,
rather than some kindly person providing an elegant VBA
code script. I know what I'm doing after I've got the SQL
string built, it's just breaking the component words into
an AND/OR string for the WHERE clause.

As I say, any guidance would be appreciated.

Thanks in advance

Phil

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Phil Latio]

Hi Bob,

Many thanks - that's certainly food for thought!

I'll ruminate on that, given the far greater flexibility it offers for
my search.

Cheers

Phil

Bob Quintal wrote:

"Phil Latio" <ph********@lycos.co.ukwrote in
news:11*********************@m7g2000cwm.googlegrou ps.com:

Hi Chris
Thanks for the quick response - good point about the
precedence. It's intended the criteria be applied to a single
memo field, so I need to think this through little.

Cheers

Phil

I built a text search similar to what you want. Had 4 textboxes
one above the other labeled "enter keyword to find" with
3comboboxes containing blank; and;or;and not; between
the textboxes. all except the first textbox were not visible.

The first textbox's afterupdate event set the first combo's
.visible to true, the first combo's after update set the second
textbox's .visible to true, and so on. If any comboboxes had
differing connectives, A little modal form would pop up and
allow the user to option

(x and y) or z
x and (y or z)

created from "(x " & cbo1 &" y) " & cbo2 & " z"
"x " & cbo1 &" (y " & cbo2 & " z)" for three variables,

(W or x) and (y or z)
W or (x and y) or z
or
(W or x or y) and z
W or x or (y and z)

then some additional code built the Where clause.

Hope this gives you some ideas.

Q

ch************@gmail.com wrote:

Well, you have to consider precedence:

Where (State = "FL" or State="AL") And SalesGuy = "JIM"

is different from:

Where State = "FL" or State="AL" And SalesGuy = "JIM"
I would almost think you'd be better off leaving a text box
and let them freeform type the where clause.
Chris Nebinger
Phil Latio wrote:
I'm kind of looking for some guidance in respect of this
obstacle I'm up against - please let me expand;

I have an option group with three options (AND, OR, & NONE)
and a text box which will accept free text - the text will
become criteria in a SQL string and depending upon the
option the WHERE clause will be AND/OR LIKE textCriteria1
AND/OR LIKE textCriteria2....

I'm trying to create a VBA function to take the free text
and break up the component words and return them back for
use in the WHERE clause.... and I'm stuck at this point!

I'm quite looking more guidance and pointers on this,
rather than some kindly person providing an elegant VBA
code script. I know what I'm doing after I've got the SQL
string built, it's just breaking the component words into
an AND/OR string for the WHERE clause.

As I say, any guidance would be appreciated.

Thanks in advance

Phil

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com