Dears,

I'd like to create a TextBox and allow users to enter an expression, for
instance,
CompanyName = 'ABC' AND DeptName = '" & C1 & "' AND EmployeeName = '" &
NamedRange1 & "'"

This is a where clause like expression and I'd like to get the cell
reference / name range reference in my code. Is there any method that I can
use to evaluate an expression and obtain the actual values and then concat to
my SQL string? Or I have to use InStr or other indirect methods to create an
expression?

Thanks a lot!

Dim s as String

s = "CompanyName = 'ABC' AND DeptName = '" & Range("C1").Value _
& "' AND EmployeeName = '" & Range("NamedRange1").Value & "'"

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Dears,

I'd like to create a TextBox and allow users to enter an expression, for
instance,
CompanyName = 'ABC' AND DeptName = '" & C1 & "' AND EmployeeName = '" &
NamedRange1 & "'"

This is a where clause like expression and I'd like to get the cell
reference / name range reference in my code. Is there any method that I
can
use to evaluate an expression and obtain the actual values and then concat
to
my SQL string? Or I have to use InStr or other indirect methods to create
an
expression?

Thanks a lot!

Thanks Tom, and is there any other automated way to do so?

Base on your solution, I still have to parse the string (from a TextBox),
locate " & __ & " characters and then change it to "Range()" property
manually.

Thanks,

"Tom Ogilvy" wrote:

Dim s as String

s = "CompanyName = 'ABC' AND DeptName = '" & Range("C1").Value _
& "' AND EmployeeName = '" & Range("NamedRange1").Value & "'"

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Dears,

I'd like to create a TextBox and allow users to enter an expression, for
instance,
CompanyName = 'ABC' AND DeptName = '" & C1 & "' AND EmployeeName = '" &
NamedRange1 & "'"

This is a where clause like expression and I'd like to get the cell
reference / name range reference in my code. Is there any method that I
can
use to evaluate an expression and obtain the actual values and then concat
to
my SQL string? Or I have to use InStr or other indirect methods to create
an
expression?

Thanks a lot!

The solution Tom gave you would create a string variable which could be
plugged straight into your SQL. Why would you need to parse that?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" wrote in message
...
Thanks Tom, and is there any other automated way to do so?

Base on your solution, I still have to parse the string (from a TextBox),
locate " & __ & " characters and then change it to "Range()" property
manually.

Thanks,

"Tom Ogilvy" wrote:

Dim s as String

s = "CompanyName = 'ABC' AND DeptName = '" & Range("C1").Value _
& "' AND EmployeeName = '" & Range("NamedRange1").Value & "'"

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Dears,

I'd like to create a TextBox and allow users to enter an expression,
for
instance,
CompanyName = 'ABC' AND DeptName = '" & C1 & "' AND EmployeeName = '"
&
NamedRange1 & "'"

This is a where clause like expression and I'd like to get the cell
reference / name range reference in my code. Is there any method that
I
can
use to evaluate an expression and obtain the actual values and then
concat
to
my SQL string? Or I have to use InStr or other indirect methods to
create
an
expression?

Thanks a lot!

It is because user won't enter a string like:
MyName = '" & Range("Name1") & "' AND ....

but they'd enter : MyName = 'ABC' AND AnotherName = '" & Name1 & "'"...

So I still have to parse the input and transform to a proper format, or am I
think in the wrong way?

THanks

"Bob Phillips" wrote:

The solution Tom gave you would create a string variable which could be
plugged straight into your SQL. Why would you need to parse that?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" wrote in message
...
Thanks Tom, and is there any other automated way to do so?

Base on your solution, I still have to parse the string (from a TextBox),
locate " & __ & " characters and then change it to "Range()" property
manually.

Thanks,

"Tom Ogilvy" wrote:

Dim s as String

s = "CompanyName = 'ABC' AND DeptName = '" & Range("C1").Value _
& "' AND EmployeeName = '" & Range("NamedRange1").Value & "'"

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Dears,

I'd like to create a TextBox and allow users to enter an expression,
for
instance,
CompanyName = 'ABC' AND DeptName = '" & C1 & "' AND EmployeeName = '"
&
NamedRange1 & "'"

This is a where clause like expression and I'd like to get the cell
reference / name range reference in my code. Is there any method that
I
can
use to evaluate an expression and obtain the actual values and then
concat
to
my SQL string? Or I have to use InStr or other indirect methods to
create
an
expression?

Thanks a lot!

I would have thought that if they are inputting the complex string that you
quote, and that they understand Names and are entering those, then getting
them to also add Range("...") would not be a problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" wrote in message
...
It is because user won't enter a string like:
MyName = '" & Range("Name1") & "' AND ....

but they'd enter : MyName = 'ABC' AND AnotherName = '" & Name1 & "'"...

So I still have to parse the input and transform to a proper format, or am
I
think in the wrong way?

THanks

"Bob Phillips" wrote:

The solution Tom gave you would create a string variable which could be
plugged straight into your SQL. Why would you need to parse that?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" wrote in message
...
Thanks Tom, and is there any other automated way to do so?

Base on your solution, I still have to parse the string (from a
TextBox),
locate " & __ & " characters and then change it to "Range()" property
manually.

Thanks,

"Tom Ogilvy" wrote:

Dim s as String

s = "CompanyName = 'ABC' AND DeptName = '" & Range("C1").Value _
& "' AND EmployeeName = '" & Range("NamedRange1").Value & "'"

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Dears,

I'd like to create a TextBox and allow users to enter an
expression,
for
instance,
CompanyName = 'ABC' AND DeptName = '" & C1 & "' AND EmployeeName =
'"
&
NamedRange1 & "'"

This is a where clause like expression and I'd like to get the
cell
reference / name range reference in my code. Is there any method
that
I
can
use to evaluate an expression and obtain the actual values and
then
concat
to
my SQL string? Or I have to use InStr or other indirect methods to
create
an
expression?

Thanks a lot!

It is because user won't enter a string like:
MyName = '" & Range("Name1") & "' AND ....

but they'd enter : MyName = 'ABC' AND AnotherName = '" & Name1 & "'"...

So I still have to parse the input and transform to a proper format, or am I
think in the wrong way?

THanks

If they enter it that way and Name1 is meant to refer to a defined name on
the worksheet, then I think you would have to parse it out and resolve it.

--
Regards,
Tom Ogilvy


"Peter" wrote:

It is because user won't enter a string like:
MyName = '" & Range("Name1") & "' AND ....

but they'd enter : MyName = 'ABC' AND AnotherName = '" & Name1 & "'"...

So I still have to parse the input and transform to a proper format, or am I
think in the wrong way?

THanks