problems passing an OR clause to a query from a textbox

[Brad Pears]

I am having a problem passing a parameter that includes an "or" clause from
a form textbox to a query...

On this form., a user can select multiple items from a list box and I want
to pass those selections to a query... What I am doing is building the
critera when the user clicks a command button like this...

txtCriteria = """" & "Criteria1" & """" & " or " & """" & "Criteria2"

When you look at the contents of txtCriteria after it has been built, you
see
"Criteria1" or "Criteria2". If I type that exact same thing into the query,
it runs perfectly. However, as soon as I change the criteria field to
reference the textbox that contains this syntax, and run it - it does not
give an error - it just returns 0 rows... ie, in the criteria field for the
query I have "forms![frmReportCriteria].[txtCriteria]".

What am I doing wrong? I have tried various incarnations of the syntax -
none seem to work....

Thanks,

Brad

 

[Douglas J. Steele]

While you can put something like "Criteria1" or "Criteria2" in the criteria
row of the query builder, you cannot point to a control that has that in it.

Take a look at the SQL that's generated under the two cases (if you're not
familiar with doing this, you want the SQL View option under the View menu).

In the first case, you'll find that the WHERE clause is something like:

WHERE MyTable.Field1 = "Criteria1" Or MyTable.Field1 = "Criteria2"

In the second case, you'll find it's:

Where MyTable.Field1 = forms![frmReportCriteria].[txtCriteria]

Two very different things...

 

[Brad Pears]

Yes, I did do that later and saw what it is evaluating to... You are
correct - definately two very different things and I can see why it is
returning no rows... Is there no way then to do this without having to
manually build the SQL clause and use that clause as the source for the
report as opposed to the query?

I was hoping that Access could "see inside" the textbox field and be able to
evaluate based on the contents as it does when you simply place one value in
a textbox field...

Thanks,

Brad

While you can put something like "Criteria1" or "Criteria2" in the
criteria row of the query builder, you cannot point to a control that has
that in it.

Take a look at the SQL that's generated under the two cases (if you're not
familiar with doing this, you want the SQL View option under the View
menu).

In the first case, you'll find that the WHERE clause is something like:

WHERE MyTable.Field1 = "Criteria1" Or MyTable.Field1 = "Criteria2"

In the second case, you'll find it's:

Where MyTable.Field1 = forms![frmReportCriteria].[txtCriteria]

Two very different things...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am having a problem passing a parameter that includes an "or" clause
from a form textbox to a query...

On this form., a user can select multiple items from a list box and I
want to pass those selections to a query... What I am doing is building
the critera when the user clicks a command button like this...

txtCriteria = """" & "Criteria1" & """" & " or " & """" & "Criteria2"

When you look at the contents of txtCriteria after it has been built, you
see
"Criteria1" or "Criteria2". If I type that exact same thing into the
query, it runs perfectly. However, as soon as I change the criteria field
to reference the textbox that contains this syntax, and run it - it does
not give an error - it just returns 0 rows... ie, in the criteria field
for the query I have "forms![frmReportCriteria].[txtCriteria]".

What am I doing wrong? I have tried various incarnations of the syntax -
none seem to work....

Thanks,

Brad

 

[Douglas J. Steele]

There are some techniques, but they require you put somewhat unusual values
in the text box (for instance, you could put ",Criteria1,Criteria2," without
the quotes, and have it interpretted correctly). I believe there's also a
way to use the Eval statement. On the whole, though, I find it easier to
dynamically write the SQL.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, I did do that later and saw what it is evaluating to... You are
correct - definately two very different things and I can see why it is
returning no rows... Is there no way then to do this without having to
manually build the SQL clause and use that clause as the source for the
report as opposed to the query?

I was hoping that Access could "see inside" the textbox field and be able
to evaluate based on the contents as it does when you simply place one
value in a textbox field...

Thanks,

Brad

While you can put something like "Criteria1" or "Criteria2" in the
criteria row of the query builder, you cannot point to a control that has
that in it.

Take a look at the SQL that's generated under the two cases (if you're
not familiar with doing this, you want the SQL View option under the View
menu).

In the first case, you'll find that the WHERE clause is something like:

WHERE MyTable.Field1 = "Criteria1" Or MyTable.Field1 = "Criteria2"

In the second case, you'll find it's:

Where MyTable.Field1 = forms![frmReportCriteria].[txtCriteria]

Two very different things...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am having a problem passing a parameter that includes an "or" clause
from a form textbox to a query...

On this form., a user can select multiple items from a list box and I
want to pass those selections to a query... What I am doing is building
the critera when the user clicks a command button like this...

txtCriteria = """" & "Criteria1" & """" & " or " & """" & "Criteria2"

When you look at the contents of txtCriteria after it has been built,
you see
"Criteria1" or "Criteria2". If I type that exact same thing into the
query, it runs perfectly. However, as soon as I change the criteria
field to reference the textbox that contains this syntax, and run it -
it does not give an error - it just returns 0 rows... ie, in the
criteria field for the query I have
"forms![frmReportCriteria].[txtCriteria]".

What am I doing wrong? I have tried various incarnations of the syntax -
none seem to work....

Thanks,

Brad