Query criteria with multiple values

[Guest]

I am trying to create a query with a criteria that will allow for multiple
values. The field in the table is "state" and the query I want will allow for
multiple states to be selected. Many thanks, John

 

[Rick B]

In your query, you have several rows down there at the bottom. Placing a
state on each line will create an "or" criteria.


FIELD: STATE FIELD2 FIELD3
Criteria: TX
Or: LA
CA
WA

 

[Guest]

Thanks Rick, but perhaps I wasn't clear or I misunderstood your instructions.

The criteria for the query is [Enter state(s)]. Right now I can only enter
one state into the query. What I want to do is have the user be able to enter
multiple states. For examply "TX, AL, FL, MS". Thanks.

 

[Guest]

Senator,

Assuming there's a UI in here, do you really want to force your user's to
enter the state abbreviations every time they need this query?

Just asking for now.
--
Chaim

Thanks Rick, but perhaps I wasn't clear or I misunderstood your instructions.

The criteria for the query is [Enter state(s)]. Right now I can only enter
one state into the query. What I want to do is have the user be able to enter
multiple states. For examply "TX, AL, FL, MS". Thanks.

In your query, you have several rows down there at the bottom. Placing a
state on each line will create an "or" criteria.


FIELD: STATE FIELD2 FIELD3
Criteria: TX
Or: LA
CA
WA

 

[Guest]

Yes, this is a "what if" query.

What does the query result look like for X, Y and Z parameters, if the
states are TX, FL, AL?

Do you have a better idea?

Thanks

Senator,

Assuming there's a UI in here, do you really want to force your user's to
enter the state abbreviations every time they need this query?

Just asking for now.
--
Chaim

Thanks Rick, but perhaps I wasn't clear or I misunderstood your instructions.

The criteria for the query is [Enter state(s)]. Right now I can only enter
one state into the query. What I want to do is have the user be able to enter
multiple states. For examply "TX, AL, FL, MS". Thanks.

In your query, you have several rows down there at the bottom. Placing a
state on each line will create an "or" criteria.


FIELD: STATE FIELD2 FIELD3
Criteria: TX
Or: LA
CA
WA






--
Rick B



I am trying to create a query with a criteria that will allow for multiple
values. The field in the table is "state" and the query I want will allow
for
multiple states to be selected. Many thanks, John

 

[Guest]

A multiselect list box which you then loop through the selected states and
incorporate them into a where clause.

Your final SQL wants to look like: where state_abbrev in ('NJ', 'GA', ...).
What you would do is add the list box control to your form and make sure
Multi Select is set to Simple or Extended (read the Access Help to determine
which you want). The user selects one or more from this list box. Then loop
through the ItemsSelected collection for the list box as (assume List Box is
named lb- I don't really like typing):

dim abb as string, i as variant, lb as control
set lb = me.listBoxControl
needComma = false
sqlStr = "select * from stateTbl where field1 in ("
for each i in lb.ItemsSelected
if needComma = false then needComma = true else sql = sql & ", "
abb = lb.ItemData(i)
sqlStr = sqlStr & "'" & abb & "'"
next
sqlStr = sqlStr & ")"

Then execute this sqlStr however (ADO Command object, RunSQL, etc.)

Good luck!
--
Chaim

Yes, this is a "what if" query.

What does the query result look like for X, Y and Z parameters, if the
states are TX, FL, AL?

Do you have a better idea?

Thanks

Senator,

Assuming there's a UI in here, do you really want to force your user's to
enter the state abbreviations every time they need this query?

Just asking for now.
--
Chaim

Thanks Rick, but perhaps I wasn't clear or I misunderstood your instructions.

The criteria for the query is [Enter state(s)]. Right now I can only enter
one state into the query. What I want to do is have the user be able to enter
multiple states. For examply "TX, AL, FL, MS". Thanks.

:

In your query, you have several rows down there at the bottom. Placing a
state on each line will create an "or" criteria.


FIELD: STATE FIELD2 FIELD3
Criteria: TX
Or: LA
CA
WA






--
Rick B



I am trying to create a query with a criteria that will allow for multiple
values. The field in the table is "state" and the query I want will allow
for
multiple states to be selected. Many thanks, John