Query multiple Criteria, pick one!

[Zach]

Hello all-
I have a query:

SELECT [Sheaves Limits Query].Ratio, [Sheaves Limits Query].ASheave,
[Sheaves Limits Query].BSheave, [Sheaves Limits Query].HPRating, [Sheaves
Limits Query].ADiameter, [Sheaves Limits Query].BDiameter, [Sheaves Limits
Query].CONVENTIONAL, [Sheaves Limits Query].ProposedBeltLength, [Sheaves
Limits Query].MinProposedBeltLength, [Sheaves Limits
Query].MaxPorposedBeltLength, [Sheaves Limits Query].ArcLength,
ArcOfContact.ArcOfContact, ArcOfContact.CorrectionFactor, Belts.PartNumber,
Belts.LinearLength, Belts.Type, Belts.LengthCorrectionFactor, [Sheaves Limits
Query].SheaveType
FROM [Sheaves Limits Query], ArcOfContact, Belts
WHERE (((ArcOfContact.ArcOfContact)=[ArcLength] Or
(ArcOfContact.ArcOfContact)>[ArcLength]-5) AND
((Belts.LinearLength)=[Proposedbeltlength] Or
(Belts.LinearLength)<[ProposedBeltLength] And
(Belts.LinearLength)>[ProposedBeltLength]-1.5) AND
((Belts.Type)=[SheaveType]));

It does some calculations for me and cross references other tables/queries
in order to run its calcs. my problem is that I need query result to be only
for the first criteria that worked. for instance.
If my criteria of:
(((ArcOfContact.ArcOfContact)=[ArcLength] Or
(ArcOfContact.ArcOfContact)>[ArcLength]-5)
gives 2 results, due to it finding one that equaled exaxtly, and also found
one within the -5, then it would show both. I want it to use the one that
equaled exactly, but if null to use the other criteria. Hope that makes
since. I realize i might not be doing it the "correct way". Just trying to
get it to work.

I've tried incorporating "Switch()" function & "Choose()" & "iif()". But
can't get the result I am looking for. Any help is greatly appreciated!!!!!!

Thanks!
Zach

 

[PieterLinden via AccessMonster.com]

Hello all-
I have a query:

SELECT [Sheaves Limits Query].Ratio, [Sheaves Limits Query].ASheave,
[Sheaves Limits Query].BSheave, [Sheaves Limits Query].HPRating, [Sheaves
Limits Query].ADiameter, [Sheaves Limits Query].BDiameter, [Sheaves Limits
Query].CONVENTIONAL, [Sheaves Limits Query].ProposedBeltLength, [Sheaves
Limits Query].MinProposedBeltLength, [Sheaves Limits
Query].MaxPorposedBeltLength, [Sheaves Limits Query].ArcLength,
ArcOfContact.ArcOfContact, ArcOfContact.CorrectionFactor, Belts.PartNumber,
Belts.LinearLength, Belts.Type, Belts.LengthCorrectionFactor, [Sheaves Limits
Query].SheaveType
FROM [Sheaves Limits Query], ArcOfContact, Belts
WHERE (((ArcOfContact.ArcOfContact)=[ArcLength] Or
(ArcOfContact.ArcOfContact)>[ArcLength]-5) AND
((Belts.LinearLength)=[Proposedbeltlength] Or
(Belts.LinearLength)<[ProposedBeltLength] And
(Belts.LinearLength)>[ProposedBeltLength]-1.5) AND
((Belts.Type)=[SheaveType]));

It does some calculations for me and cross references other tables/queries
in order to run its calcs. my problem is that I need query result to be only
for the first criteria that worked. for instance.
If my criteria of:
(((ArcOfContact.ArcOfContact)=[ArcLength] Or
(ArcOfContact.ArcOfContact)>[ArcLength]-5)
gives 2 results, due to it finding one that equaled exaxtly, and also found
one within the -5, then it would show both. I want it to use the one that
equaled exactly, but if null to use the other criteria. Hope that makes
since. I realize i might not be doing it the "correct way". Just trying to
get it to work.

I've tried incorporating "Switch()" function & "Choose()" & "iif()". But
can't get the result I am looking for. Any help is greatly appreciated!!!!!!

Thanks!
Zach

One way might be to have separate queries for the mutually exclusive options
and open them from a form. Then you can evaluate the criteria entered and
choose the correct query to run in code. If you based a form on the
unfiltered query, you could select options on your unbound form, and build
the filter on the fly and pass the filter string you just built in when you
open the form (DoCmd.OpenForm...) near the end of the list of arguments is
the filter... just reference your filter string there.

HTH,
Pieter