Prev: Query Help One-to-Many
Next: Count if Text is Equal in two Fields
From: Zach on 24 Mar 2010 14:32
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
From: PieterLinden via AccessMonster.com on 24 Mar 2010 17:18
Zach wrote:
>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

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Query Help One-to-Many
Next: Count if Text is Equal in two Fields