Query from field w/multiple values

[Guest]

Is there a way to set up a field so that a query will read the value(s) as
criteria?
I use [plant] but that seems to only allow me to enter one value. Can I get
it to be able to enter two or three? If I can not do it that way is there a
way to get the query to change a value for the criteria?

 

[Guest]

Hi James,

Using the "[Criteria]" should let you change the value everytime when you
execute your query.

There is another way that you can query more than one value.
You can build a table that will contain all the values that you want to
retrieve in a single field. Then you can JOIN them and get your records back.

Hope this helps.

 

[Guest]

What I mean is can I enter multiple criteria in the same criteria request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two plant
codes, say 41 and 44, it will return nothing. I want the operator to be able
to enter any number of plant codes they need in the same criteria field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple criteria in
the same criteria request box for the query.

Hi James,

Using the "[Criteria]" should let you change the value everytime when you
execute your query.

There is another way that you can query more than one value.
You can build a table that will contain all the values that you want to
retrieve in a single field. Then you can JOIN them and get your records back.

Hope this helps.

Is there a way to set up a field so that a query will read the value(s) as
criteria?
I use [plant] but that seems to only allow me to enter one value. Can I get
it to be able to enter two or three? If I can not do it that way is there a
way to get the query to change a value for the criteria?

 

[Guest]

Hi James,

I have not being able to enter more than 1 value in a textbox and make query
to use "IN" operator to read them. Maybe someone in the newgroup have done
it before.

As I have mentioned in my note, I can get around the multiple value by store
them in a table and execute the query based on that table.

Hope this clears up or make it even more muddy.

What I mean is can I enter multiple criteria in the same criteria request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two plant
codes, say 41 and 44, it will return nothing. I want the operator to be able
to enter any number of plant codes they need in the same criteria field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple criteria in
the same criteria request box for the query.

Hi James,

Using the "[Criteria]" should let you change the value everytime when you
execute your query.

There is another way that you can query more than one value.
You can build a table that will contain all the values that you want to
retrieve in a single field. Then you can JOIN them and get your records back.

Hope this helps.

Is there a way to set up a field so that a query will read the value(s) as
criteria?
I use [plant] but that seems to only allow me to enter one value. Can I get
it to be able to enter two or three? If I can not do it that way is there a
way to get the query to change a value for the criteria?

 

[PC Datasheet]

Change your Where statement to:
WHERE [Scrap Data].[Plant Code]=[Enter Plant1 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant2 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant3 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant4 number]

Use as many "Or" statements as you have plants.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I mean is can I enter multiple criteria in the same criteria request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two plant
codes, say 41 and 44, it will return nothing. I want the operator to be able
to enter any number of plant codes they need in the same criteria field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple criteria in
the same criteria request box for the query.

Hi James,

Using the "[Criteria]" should let you change the value everytime when you
execute your query.

There is another way that you can query more than one value.
You can build a table that will contain all the values that you want to
retrieve in a single field. Then you can JOIN them and get your records back.

Hope this helps.

Is there a way to set up a field so that a query will read the value(s) as
criteria?
I use [plant] but that seems to only allow me to enter one value. Can I get
it to be able to enter two or three? If I can not do it that way is there a
way to get the query to change a value for the criteria?

 

[Guest]

Ok this works for the query. How do I get it to recognize the information
from one criteria field in a form? This is the hard part. Do I have to make
two criteria fields in the form or is there a way to enter all the plant
codes necessary in the one criteria field, which is what is needed?

Change your Where statement to:
WHERE [Scrap Data].[Plant Code]=[Enter Plant1 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant2 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant3 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant4 number]

Use as many "Or" statements as you have plants.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I mean is can I enter multiple criteria in the same criteria request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two plant
codes, say 41 and 44, it will return nothing. I want the operator to be able
to enter any number of plant codes they need in the same criteria field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple criteria in
the same criteria request box for the query.

Hi James,

Using the "[Criteria]" should let you change the value everytime when you
execute your query.

There is another way that you can query more than one value.
You can build a table that will contain all the values that you want to
retrieve in a single field. Then you can JOIN them and get your records back.

Hope this helps.


:

Is there a way to set up a field so that a query will read the value(s) as
criteria?
I use [plant] but that seems to only allow me to enter one value. Can I get
it to be able to enter two or three? If I can not do it that way is there a
way to get the query to change a value for the criteria?

 

[Brendan Reynolds]

Check out the topic "Use Multi-Select List boxes as query parameters" at the
following URL ...

http://www.mvps.org/access/forms/frm0007.htm

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ok this works for the query. How do I get it to recognize the information
from one criteria field in a form? This is the hard part. Do I have to
make
two criteria fields in the form or is there a way to enter all the plant
codes necessary in the one criteria field, which is what is needed?

Change your Where statement to:
WHERE [Scrap Data].[Plant Code]=[Enter Plant1 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant2 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant3 number] Or _
[Scrap Data].[Plant Code]=[Enter Plant4 number]

Use as many "Or" statements as you have plants.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

What I mean is can I enter multiple criteria in the same criteria
request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two
plant
codes, say 41 and 44, it will return nothing. I want the operator to
be able
to enter any number of plant codes they need in the same criteria
field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able
to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple
criteria in
the same criteria request box for the query.

:

Hi James,

Using the "[Criteria]" should let you change the value everytime when you
execute your query.

There is another way that you can query more than one value.
You can build a table that will contain all the values that you want
to
retrieve in a single field. Then you can JOIN them and get your
records back.

Hope this helps.


:

Is there a way to set up a field so that a query will read the value(s) as
criteria?
I use [plant] but that seems to only allow me to enter one value.
Can I get
it to be able to enter two or three? If I can not do it that way
is there a
way to get the query to change a value for the criteria?

 

[Marshall Barton]

What I mean is can I enter multiple criteria in the same criteria request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two plant
codes, say 41 and 44, it will return nothing. I want the operator to be able
to enter any number of plant codes they need in the same criteria field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple criteria in
the same criteria request box for the query.

Allowing for some possible ambiguity with partial code
numbers, you could use:

WHERE InStr([Enter Plant number(s)], [Plant Code]) > 1

 

[Guest]

Thanks you all for your help. This one just seems to be over my head so I am
looking to break it down and will do it in a way I understand. Thanks again.
I will study this in the future so that I will be able to learn it good
enough to use it.

James

What I mean is can I enter multiple criteria in the same criteria request?
For example I have a query that asks for a plant code. I can enter one
number, say 41, and it will query just fine. If I try to enter two plant
codes, say 41 and 44, it will return nothing. I want the operator to be able
to enter any number of plant codes they need in the same criteria field.

If I wrote it in SQL with hard entered plant codes it would look like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=41)) OR ((([Scrap Data].[Plant Code])=44))
GROUP BY [Scrap Data].[Plant Code]

If I write it in user entry format it looks like this:
SELECT [Scrap Data].[Plant Code], Count([Scrap Data].[Plant Code]) AS
[CountOfPlant Code]
FROM [Scrap Data]
WHERE ((([Scrap Data].[Plant Code])=[Enter Plant number(s)]))
GROUP BY [Scrap Data].[Plant Code];

is there any way to make the criteria [Enter Plant number(s)] be able to
allow multiple plants so that the query will run it like the first mentioned
SQL if I entered plant codes 41 and 44? Meaning enter multiple criteria in
the same criteria request box for the query.

Allowing for some possible ambiguity with partial code
numbers, you could use:

WHERE InStr([Enter Plant number(s)], [Plant Code]) > 1