Defining a criteria for a query from a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a textbox on my form. On that text box, with VBA, I am creating a
text line that will be linked to my query for the criteria.
My problem is:
If there is one value Lets say ANDY in the text box and when I insert that
text box name in the name criteria in the query it is working.
But if I have ANDY OR JACK, then the query is not working. If I type
manually the criteria to the query, it works. I also tried to modify the VBA
code so the text box would be "ANDY" OR "JACK", but still does not work.
I know it must be a simple quesotion for some of you.
Do you have any suggestions, on what I can do to make this query work..

Thanks
 
Jack said:
I have a textbox on my form. On that text box, with VBA, I am creating a
text line that will be linked to my query for the criteria.
My problem is:
If there is one value Lets say ANDY in the text box and when I insert that
text box name in the name criteria in the query it is working.
But if I have ANDY OR JACK, then the query is not working. If I type
manually the criteria to the query, it works. I also tried to modify the VBA
code so the text box would be "ANDY" OR "JACK", but still does not work.
I know it must be a simple quesotion for some of you.
Click to expand...


Maybe a simple question, but definitely not a simple answer.

What you can type into the Criteria row of the QBE is
translated by Access into a valid SQL Where condition. So
when you provide the criteria:

ANDY OR JACK

it is converted to:

Fname = "Andy" OR Fname = "Jack"

That kind of translation is a very sophisticated operation
that you really do not want to try to duplicate.

If you really want to provide your users with that kind of
flexibility, then you have to do two things.

1) Use the BuildCriteria function to translate the user's
criteria into a valid Where condition string. See Help for
details.

2) Use VBA code construct your own SQL statement from the
Where condition strings in the first step.

You can then use the resulting SQL statement instead of a
saved query.
 
Hi,
I have a textbox on my form. On that text box, with VBA, I am creating a
text line that will be linked to my query for the criteria.
My problem is:
If there is one value Lets say ANDY in the text box and when I insert that
text box name in the name criteria in the query it is working.
But if I have ANDY OR JACK, then the query is not working. If I type
manually the criteria to the query, it works. I also tried to modify the VBA
code so the text box would be "ANDY" OR "JACK", but still does not work.
I know it must be a simple quesotion for some of you.
Do you have any suggestions, on what I can do to make this query work..

Thanks
Click to expand...

You can pass *values* in a parameter, but you cannot pass *operators*
such as OR or IN. If you want to allow the user to enter any valid SQL
expression (and if you trust them to do so accurately... a big IF!)
you'll need to write VBA code to construct the SQL string
concatenating the value entered into the textbox.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top