need number in Criteria not enter number

[Guest]

I have a Database that the Criteria has [enter districts] or [enter
districts] or [enter districts]. So every time I type in three numbers I
want. “I have 18 centers and in every center has 3 districts. I have to do
this 18 times.†I would like to make a Marco, but I do not know how to make
the Criteria, so I do not have to type the three districts in, (example 24,
39, and 41) in one query then move to another query and give me 4, 5 and 20
and etc. Can you tell me in the Criteria part how to put just the number
type in this Criterion? I tried =24 “on one line†then =39 the next, and the
three line =41 but does not work.

 

[John Vinson]

I have a Database that the Criteria has [enter districts] or [enter
districts] or [enter districts]. So every time I type in three numbers I
want. “I have 18 centers and in every center has 3 districts. I have to do
this 18 times.” I would like to make a Marco, but I do not know how to make
the Criteria, so I do not have to type the three districts in, (example 24,
39, and 41) in one query then move to another query and give me 4, 5 and 20
and etc. Can you tell me in the Criteria part how to put just the number
type in this Criterion? I tried =24 “on one line” then =39 the next, and the
three line =41 but does not work.

If you use the same parameter multiple times, you'll only get prompted
once. Try using three DIFFERENT prompts - [Enter first district],
[Enter second district], [Enter third district] on three separate
lines of the query grid for example.

Or... better... create a little unbound Form, named frmCrit say, with
three textboxes txtDist1, txtDist2, txtDist3. Use criteria

=[Forms]![frmCrit]![txtDist1]
=[Forms]![frmCrit]![txtDist2]
=[Forms]![frmCrit]![txtDist3]

on three separate lines of the query grid.

I'm not at all certain how the "centers" get involved. Are you putting
in other criteria for the center? or do the districts depend on the
center?

John W. Vinson[MVP]

 

[Conan Kelly]

rickburns,

I would put all three districts on one line, kind of like your parameter criteria:

Replace "[enter districts] or [enter districts] or [enter districts]" with "24 Or 39 Or 41"

This is pretty much saying the same thing as you have written below, ONLY if you have no other criteria for other fields.

If you do have criteria for other fields on the first criteria line and the 3 districts on 3 different lines, then what you are
telling Access to query is:

(district = 24 AND all other criteria) OR (district = 39) OR (district = 41)

when what you want to tell access to query is:

((district = 24 OR district = 39 OR district = 41) AND all other criteria)



Another way to simplfy the query would be to add a "Center" field to the table. Then for all records with districts of 24, 39, or
41, the "Center" field could be set to 1...all records with districts of 4, 5, or 20 could have the "Center" field set to 2...and so
on and so forth. Then your queries could be set to return "Center 1" instead of "Districts 24, 39, and 41".

I hope this helps,

Conan Kelly

 

[John Vinson]

Replace "[enter districts] or [enter districts] or [enter districts]" with "24 Or 39 Or 41"

Actually this will return all districts: the OR operator treats any
nonzero value as TRUE, so it will return TRUE Or TRUE Or True.

A better syntax would be

IN (24, 39, 41)

but this unfortunately doesn't work with parameter queries.

John W. Vinson[MVP]

 

[Guest]

I'm sorry, I missing something: HERE are the firelds I have-- State Date,
which has a criteria <> #1/1/1991# -- Route which has criteria Not like"*01"
And Not like "*02" -- Name Which has a criteria like "OPEN*", and then the
one this message is about. So basely I am tell you that all the Feilds are
criterias
thank you
--
rickburns

Replace "[enter districts] or [enter districts] or [enter districts]" with "24 Or 39 Or 41"

Actually this will return all districts: the OR operator treats any
nonzero value as TRUE, so it will return TRUE Or TRUE Or True.

A better syntax would be

IN (24, 39, 41)

but this unfortunately doesn't work with parameter queries.

John W. Vinson[MVP]

 

[John Vinson]

I'm sorry, I missing something: HERE are the firelds I have-- State Date,
which has a criteria <> #1/1/1991# -- Route which has criteria Not like"*01"
And Not like "*02" -- Name Which has a criteria like "OPEN*", and then the
one this message is about. So basely I am tell you that all the Feilds are
criterias
thank you

I'm sorry, this isn't making sense to me.

You say "all the fields are criterias". You can APPLY criteria to a
field but fields are *not* criteria. And this has apparently nothing
to do with your original post.

Could you please open the Query in SQL view and post the SQL text to a
message here?

John W. Vinson[MVP]

 

[Guest]

SELECT [MASTER CARRIER FILE 1]!DIST AS Expr1, [MASTER CARRIER FILE 1]!CARRIER
AS Expr2, [MASTER CARRIER FILE 1]!NAME AS Expr3

FROM [MASTER CARRIER FILE 1]

WHERE ((([MASTER CARRIER FILE 1].[START DATE])<>#1/1/1991#) AND (([MASTER
CARRIER FILE 1]![DIST])=[enter district] Or ([MASTER CARRIER FILE
1]![DIST])=[enter district2] Or ([MASTER CARRIER FILE 1]![DIST])=[enter
district3]) AND (([MASTER CARRIER FILE 1].ROUTE) Not Like "*01" And ([MASTER
CARRIER FILE 1].ROUTE) Not Like "* 02")) OR ((([MASTER CARRIER FILE
1]![DIST])=[enter district] Or ([MASTER CARRIER FILE 1]![DIST])=[enter
district2] Or ([MASTER CARRIER FILE 1]![DIST])=[enter district3]) AND
(([MASTER CARRIER FILE 1].NAME) Like "OPEN*"))

ORDER BY [MASTER CARRIER FILE 1]!DIST, [MASTER CARRIER FILE 1]!CARRIER,
[MASTER CARRIER FILE 1].[START DATE] DESC;

 

[Guest]

Can you help?
--
rickburns

SELECT [MASTER CARRIER FILE 1]!DIST AS Expr1, [MASTER CARRIER FILE 1]!CARRIER
AS Expr2, [MASTER CARRIER FILE 1]!NAME AS Expr3

FROM [MASTER CARRIER FILE 1]

WHERE ((([MASTER CARRIER FILE 1].[START DATE])<>#1/1/1991#) AND (([MASTER
CARRIER FILE 1]![DIST])=[enter district] Or ([MASTER CARRIER FILE
1]![DIST])=[enter district2] Or ([MASTER CARRIER FILE 1]![DIST])=[enter
district3]) AND (([MASTER CARRIER FILE 1].ROUTE) Not Like "*01" And ([MASTER
CARRIER FILE 1].ROUTE) Not Like "* 02")) OR ((([MASTER CARRIER FILE
1]![DIST])=[enter district] Or ([MASTER CARRIER FILE 1]![DIST])=[enter
district2] Or ([MASTER CARRIER FILE 1]![DIST])=[enter district3]) AND
(([MASTER CARRIER FILE 1].NAME) Like "OPEN*"))

ORDER BY [MASTER CARRIER FILE 1]!DIST, [MASTER CARRIER FILE 1]!CARRIER,
[MASTER CARRIER FILE 1].[START DATE] DESC;

--
rickburns

I'm sorry, this isn't making sense to me.

You say "all the fields are criterias". You can APPLY criteria to a
field but fields are *not* criteria. And this has apparently nothing
to do with your original post.

Could you please open the Query in SQL view and post the SQL text to a
message here?

John W. Vinson[MVP]