formula

[Revned]

Hi,

i have a select query with the following SQL

SELECT tblOrder.CustomerID, [GivenName] & " " & [Surname] AS Name,
tblCustomer.SchedDelivery, tblOrder.OrderQty, tblOrder.RefType,
tblOrder.RefNo, tblOrder.Date, tblOrder.Amount, tblOrder.RefTyp_Pay,
tblOrder.RefNo_Pay, tblOrder.PayAmt, [Amount]-[PayAmt] AS Bal, tblOrder.Status
FROM tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerID =
tblOrder.CustomerID
WHERE (((tblOrder.OrderQty) Is Not Null));

I need a formula that when [Bal] field is equal to zero
it will display "Closed" under [Status] field
and displays "Open" when [Bal] field is in IsNull.

how to accomplish this one, can any is kind of helping in figuring this out.

thanks

 

[Jeff Boyce]

Are you trying to alter the value in your table based on the contents of
[Bal], or are you only displaying values?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

SELECT tblOrder.CustomerID
, [GivenName] & " " & [Surname] AS Name
, tblCustomer.SchedDelivery
, tblOrder.OrderQty
, tblOrder.RefType
, tblOrder.RefNo
, tblOrder.Date
, tblOrder.Amount
, tblOrder.RefTyp_Pay
, tblOrder.RefNo_Pay
, tblOrder.PayAmt
, [Amount]-[PayAmt] AS Bal
, tblOrder.Status

, IIF([Amount]-[PayAmt]=0,"Closed","Open") as PaymentState

FROM tblCustomer INNER JOIN tblOrder
ON tblCustomer.CustomerID = tblOrder.CustomerID
WHERE (((tblOrder.OrderQty) Is Not Null));

It is possible that Amount-PayAmt may be close to zero but not exactly zero
depending on your field types. If you have a problem you can change the IIF
expression to

IIF(Abs(Amount-PayAmt)<.00001,"Closed","Open")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dorian]

You don't say what to do if neither condition is true so I use 'Other'.

SELECT tblOrder.CustomerID, [GivenName] & " " & [Surname] AS Name,
tblCustomer.SchedDelivery, tblOrder.OrderQty, tblOrder.RefType,
tblOrder.RefNo, tblOrder.Date, tblOrder.Amount, tblOrder.RefTyp_Pay,
tblOrder.RefNo_Pay, tblOrder.PayAmt, [tblOrder.Amount]-[tblOrder.PayAmt] AS
Bal,

IIF(tblOrder.Status=0,'Closed',IIF(isnull([tblOrder.Amount]-[tblOrder.PayAmt]),'Open','Other')) As OrderStatus

FROM tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerID =
tblOrder.CustomerID
WHERE (((tblOrder.OrderQty) Is Not Null));
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[theDBguy]

Hi,

It sounds like that "Status" is a calculated field, which means you don't
need to save it in your table. Using a query, you can try something like:

SELECT ..., IIf([bal]=0, "Closed", "Open") As Status
FROM ...
WHERE ...

Hope that helps...

 

[Jerry Whittle]

You don't say what you want when it's not null or 0. Below will say Open if
null and closed otherwise.

IIf(IsNull([Amount]-[PayAmt]) = True, "Open", "Closed")

This one will say Open when null, Closed when 0, and give the Bal when
neither null or 0.

IIf(IsNull([Amount]-[PayAmt]) = True, "Open", IIf([Amount]-[PayAmt]) = 0,
"Closed", ([Amount]-[PayAmt]))

 

[Revned]

Guys Thank You, I really learned from it...
I appreciate too your time

thanks again

You don't say what you want when it's not null or 0. Below will say Open if
null and closed otherwise.

IIf(IsNull([Amount]-[PayAmt]) = True, "Open", "Closed")

This one will say Open when null, Closed when 0, and give the Bal when
neither null or 0.

IIf(IsNull([Amount]-[PayAmt]) = True, "Open", IIf([Amount]-[PayAmt]) = 0,
"Closed", ([Amount]-[PayAmt]))

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

i have a select query with the following SQL

SELECT tblOrder.CustomerID, [GivenName] & " " & [Surname] AS Name,
tblCustomer.SchedDelivery, tblOrder.OrderQty, tblOrder.RefType,
tblOrder.RefNo, tblOrder.Date, tblOrder.Amount, tblOrder.RefTyp_Pay,
tblOrder.RefNo_Pay, tblOrder.PayAmt, [Amount]-[PayAmt] AS Bal, tblOrder.Status
FROM tblCustomer INNER JOIN tblOrder ON tblCustomer.CustomerID =
tblOrder.CustomerID
WHERE (((tblOrder.OrderQty) Is Not Null));

I need a formula that when [Bal] field is equal to zero
it will display "Closed" under [Status] field
and displays "Open" when [Bal] field is in IsNull.

how to accomplish this one, can any is kind of helping in figuring this out.

thanks