variable criteria

[Karen H]

Hi chaps,

I have a query that runs from a form. The form has a
combo box on it where the top row says "ALL COUNTRIES" and
below that are the countries alphabetically listed.

In the country field in my query, I want to put in the
combobox as a criteria if something other than "ALL
COUNTRIES" is selected... otherwise I don't want a
criteria at all. I want to reference the query directly
rather than building an SQL string in VBA. How can I do
this?

Thanks guys,
K x

 

[Tom Ellison]

Dear Karen:

How about this:

WHERE (Country = [Forms]![YourForm]![YourCombo]
OR [Forms]![YourForm]![YourCombo] = "ALL COUNTRIES")

That is, if the combo says "ALL COUNTRIES" or if the country is the
one in the combo box. Just a slightly different way of saying it, but
logically equivalent.

I recommend you make "ALL COUNTRIES" the combo box default so it won't
just be left blank.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Karen]

Thanks for the suggestion - but it doesn't seem to work.
If I put that sql in the sql view and show the data it
simply deletes the where clause that I entered.

Any other ideas??

K xx

-----Original Message-----
Dear Karen:

How about this:

WHERE (Country = [Forms]![YourForm]![YourCombo]
OR [Forms]![YourForm]![YourCombo] = "ALL COUNTRIES")

That is, if the combo says "ALL COUNTRIES" or if the country is the
one in the combo box. Just a slightly different way of saying it, but
logically equivalent.

I recommend you make "ALL COUNTRIES" the combo box default so it won't
just be left blank.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi chaps,

I have a query that runs from a form. The form has a
combo box on it where the top row says "ALL COUNTRIES" and
below that are the countries alphabetically listed.

In the country field in my query, I want to put in the
combobox as a criteria if something other than "ALL
COUNTRIES" is selected... otherwise I don't want a
criteria at all. I want to reference the query directly
rather than building an SQL string in VBA. How can I do
this?

Thanks guys,
K x

.

 

[Tom Ellison]

Dear Karen:

This WHERE clause, as with most SQL text posted here, is not designed
to go into the design grid. It can be placed into the SQL view of
your query. You may need a bit of knowledge how to handle SQL syntax
to do this. If that's a problem, you should post the SQL of the
entire query you have so I can see it and make the changes to it as a
whole, instead of in pieces like this.

Post back the SQL you originally had and I'll try to work from that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Thanks for the suggestion - but it doesn't seem to work.
If I put that sql in the sql view and show the data it
simply deletes the where clause that I entered.

Any other ideas??

K xx

-----Original Message-----
Dear Karen:

How about this:

WHERE (Country = [Forms]![YourForm]![YourCombo]
OR [Forms]![YourForm]![YourCombo] = "ALL COUNTRIES")

That is, if the combo says "ALL COUNTRIES" or if the country is the
one in the combo box. Just a slightly different way of saying it, but
logically equivalent.

I recommend you make "ALL COUNTRIES" the combo box default so it won't
just be left blank.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi chaps,

I have a query that runs from a form. The form has a
combo box on it where the top row says "ALL COUNTRIES" and
below that are the countries alphabetically listed.

In the country field in my query, I want to put in the
combobox as a criteria if something other than "ALL
COUNTRIES" is selected... otherwise I don't want a
criteria at all. I want to reference the query directly
rather than building an SQL string in VBA. How can I do
this?

Thanks guys,
K x

.

 

[Karen]

Got it!

I was putting it in the SQL view - I think the problem was
that it was quite a big SQL statement and I didn't do
enough to accommodate the other WHERE statements that were
already in place.

Was interesting to see how it looked in the design grid
too - didn't think of doing things like that. Thanks
loads Tom - you clearly are my 1 stop IT expert!

K xx

-----Original Message-----
Dear Karen:

This WHERE clause, as with most SQL text posted here, is not designed
to go into the design grid. It can be placed into the SQL view of
your query. You may need a bit of knowledge how to handle SQL syntax
to do this. If that's a problem, you should post the SQL of the
entire query you have so I can see it and make the changes to it as a
whole, instead of in pieces like this.

Post back the SQL you originally had and I'll try to work from that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Thanks for the suggestion - but it doesn't seem to work.
If I put that sql in the sql view and show the data it
simply deletes the where clause that I entered.

Any other ideas??

K xx

-----Original Message-----
Dear Karen:

How about this:

WHERE (Country = [Forms]![YourForm]![YourCombo]
OR [Forms]![YourForm]![YourCombo] = "ALL COUNTRIES")

That is, if the combo says "ALL COUNTRIES" or if the country is the
one in the combo box. Just a slightly different way of saying it, but
logically equivalent.

I recommend you make "ALL COUNTRIES" the combo box default so it won't
just be left blank.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 12 Jan 2004 10:26:57 -0800, "Karen H"

Hi chaps,

I have a query that runs from a form. The form has a
combo box on it where the top row says "ALL COUNTRIES" and
below that are the countries alphabetically listed.

In the country field in my query, I want to put in the
combobox as a criteria if something other than "ALL
COUNTRIES" is selected... otherwise I don't want a
criteria at all. I want to reference the query directly
rather than building an SQL string in VBA. How can I do
this?

Thanks guys,
K x

.

.