IIF statement in query criteria

[Guest]

Hi,

I tried searching the forum for help before I posted but I came up with
nothing. I need some help with a query. I have a query that shows
custermers in a certain region with account due within 90 days. In the
criteria of my query I exclude current balance that are less or equal to zero
than zero.
In this query I also need to show loans within that region that are zero
balance. I am including these account by account type criteria on an OR
condition. When I do it like this it give me account for other regions.

Do I need to be doing this with a iif statement in my criteria?

Here is a part of my query to give a better understanding

WHERE (((OfficerRegion.Region)="Central") AND
((AccountSetup.Maturity)<=DateAdd("d",90,Date())) AND
(((LnAcctg.CurrentBal)>0)) OR ((AccountSetup.AccountType) In (60,61,62,63)))

Thanks for you help...

 

[Guest]

Try this version. The problem you were having is that the Region only
applied to the left side of the OR. In the example below, the region and
maturity date will apply to both sides because of how the parenthesis are
used.
(most of the parens created by the query builder are extraneous)

WHERE (OfficerRegion.Region = "Central" AND AccountSetup.Maturity <
DateAdd("d",90,Date())) AND (lnAcctg.CurrentBal > 0 OR
AccountSetup.AccountType In (60,61,62,63))

 

[Marshall Barton]

I tried searching the forum for help before I posted but I came up with
nothing. I need some help with a query. I have a query that shows
custermers in a certain region with account due within 90 days. In the
criteria of my query I exclude current balance that are less or equal to zero
than zero.
In this query I also need to show loans within that region that are zero
balance. I am including these account by account type criteria on an OR
condition. When I do it like this it give me account for other regions.

Do I need to be doing this with a iif statement in my criteria?

Here is a part of my query to give a better understanding

WHERE (((OfficerRegion.Region)="Central") AND
((AccountSetup.Maturity)<=DateAdd("d",90,Date())) AND
(((LnAcctg.CurrentBal)>0)) OR ((AccountSetup.AccountType) In (60,61,62,63)))

I think your parenthesis are wrong. Try this:

WHERE OfficerRegion.Region="Central"
AND (AccountSetup.Maturity<=DateAdd("d",90,Date())
AND LnAcctg.CurrentBal>0))
OR AccountSetup.AccountType In (60,61,62,63))

 

[John Spencer]

WHERE (OfficerRegion.Region="Central" AND
AccountSetup.Maturity<=DateAdd("d",90,Date())
AND LnAcctg.CurrentBal>0
OR
(AccountSetup.AccountType In (60,61,62,63)
AND OfficerRegion.Region="Central" )

Alternative
WHERE (OfficerRegion.Region="Central" )
AND ((AccountSetup.Maturity<=DateAdd("d",90,Date())
AND LnAcctg.CurrentBal>0)
OR
AccountSetup.AccountType In (60,61,62,63))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..