MS Access 2000/2003

[Guest]

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")

 

[Guest]

Thanks Tom for your reply. May I please ask - where do I put this statement
- in the Query or in the data entry form? I tried in both places but I get
"-1" for all the entries in the Status field and not "open" or "closed".

Can you correct me please? Thanks - Ozzie

Hi Ozzie,

The statement is correct only if all fields are guaranteed to have some
value. To keep the calculation working if any of the fields are null, you can
use the Nz function to convert a null to zero, as in this example:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")

 

[Guest]

Hi Ozzie,

It can go either in a query, or it can be entered as the Control Source for
a calculated textbox. If you put it into a query, the entire line goes into
the row labelled "Field" in the QBE (Query by Example) grid.

If you are still having trouble, I will take a look at your database if you
send me a compacted and zipped copy. If you are interested, send me a private
e-mail message with a valid reply-to address. My e-mail address is available
at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. May I please ask - where do I put this statement
- in the Query or in the data entry form? I tried in both places but I get
"-1" for all the entries in the Status field and not "open" or "closed".

Can you correct me please? Thanks - Ozzie

Hi Ozzie,

The statement is correct only if all fields are guaranteed to have some
value. To keep the calculation working if any of the fields are null, you can
use the Nz function to convert a null to zero, as in this example:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")

 

[Guest]

Hi Tom - have done as per your 2nd para. Hopefully you can spot the problem.

Thanks - Ozzie

Hi Ozzie,

It can go either in a query, or it can be entered as the Control Source for
a calculated textbox. If you put it into a query, the entire line goes into
the row labelled "Field" in the QBE (Query by Example) grid.

If you are still having trouble, I will take a look at your database if you
send me a compacted and zipped copy. If you are interested, send me a private
e-mail message with a valid reply-to address. My e-mail address is available
at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. May I please ask - where do I put this statement
- in the Query or in the data entry form? I tried in both places but I get
"-1" for all the entries in the Status field and not "open" or "closed".

Can you correct me please? Thanks - Ozzie

Hi Ozzie,

The statement is correct only if all fields are guaranteed to have some
value. To keep the calculation working if any of the fields are null, you can
use the Nz function to convert a null to zero, as in this example:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")

 

[Guest]

Hi Ozzie,

You had entered:
STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")=IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")

You need to eliminate the second part that starts with =IIf, so that it
reads like this:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")

It may not be so obvious that you have this double entry by looking at the
field in query design. Place your cursor into this field, and then press
Shift F2 to open a zoom box.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom - have done as per your 2nd para. Hopefully you can spot the problem.

Thanks - Ozzie

Hi Ozzie,

It can go either in a query, or it can be entered as the Control Source for
a calculated textbox. If you put it into a query, the entire line goes into
the row labelled "Field" in the QBE (Query by Example) grid.

If you are still having trouble, I will take a look at your database if you
send me a compacted and zipped copy. If you are interested, send me a private
e-mail message with a valid reply-to address. My e-mail address is available
at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. May I please ask - where do I put this statement
- in the Query or in the data entry form? I tried in both places but I get
"-1" for all the entries in the Status field and not "open" or "closed".

Can you correct me please? Thanks - Ozzie

:

Hi Ozzie,

The statement is correct only if all fields are guaranteed to have some
value. To keep the calculation working if any of the fields are null, you can
use the Nz function to convert a null to zero, as in this example:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")

 

[Guest]

Thank you the Microsoft community (what a great site) where I was replied by
the expert Mr. Tom Wickerath. His prompt assistance was of immense help to
me in resolving the problem (I struggled for 2 days and was desparate for
help).

Once again my sincere thanks to you Tom (MVP). I missed out on the rating,
but would rate it as the highest.

Ozzie

Hi Ozzie,

You had entered:
STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")=IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")

You need to eliminate the second part that starts with =IIf, so that it
reads like this:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")

It may not be so obvious that you have this double entry by looking at the
field in query design. Place your cursor into this field, and then press
Shift F2 to open a zoom box.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom - have done as per your 2nd para. Hopefully you can spot the problem.

Thanks - Ozzie

Hi Ozzie,

It can go either in a query, or it can be entered as the Control Source for
a calculated textbox. If you put it into a query, the entire line goes into
the row labelled "Field" in the QBE (Query by Example) grid.

If you are still having trouble, I will take a look at your database if you
send me a compacted and zipped copy. If you are interested, send me a private
e-mail message with a valid reply-to address. My e-mail address is available
at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom for your reply. May I please ask - where do I put this statement
- in the Query or in the data entry form? I tried in both places but I get
"-1" for all the entries in the Status field and not "open" or "closed".

Can you correct me please? Thanks - Ozzie

:

Hi Ozzie,

The statement is correct only if all fields are guaranteed to have some
value. To keep the calculation working if any of the fields are null, you can
use the Nz function to convert a null to zero, as in this example:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")

 

[Guest]

I'm not an MVP yet. Hopeful, yes. But not quite there.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thank you the Microsoft community (what a great site) where I was replied by
the expert Mr. Tom Wickerath. His prompt assistance was of immense help to
me in resolving the problem (I struggled for 2 days and was desparate for
help).

Once again my sincere thanks to you Tom (MVP). I missed out on the rating,
but would rate it as the highest.

Ozzie

Hi Ozzie,

You had entered:
STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")=IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")

You need to eliminate the second part that starts with =IIf, so that it
reads like this:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")

It may not be so obvious that you have this double entry by looking at the
field in query design. Place your cursor into this field, and then press
Shift F2 to open a zoom box.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom - have done as per your 2nd para. Hopefully you can spot the problem.

Thanks - Ozzie

:

Hi Ozzie,

It can go either in a query, or it can be entered as the Control Source for
a calculated textbox. If you put it into a query, the entire line goes into
the row labelled "Field" in the QBE (Query by Example) grid.

If you are still having trouble, I will take a look at your database if you
send me a compacted and zipped copy. If you are interested, send me a private
e-mail message with a valid reply-to address. My e-mail address is available
at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks Tom for your reply. May I please ask - where do I put this statement
- in the Query or in the data entry form? I tried in both places but I get
"-1" for all the entries in the Status field and not "open" or "closed".

Can you correct me please? Thanks - Ozzie

:

Hi Ozzie,

The statement is correct only if all fields are guaranteed to have some
value. To keep the calculation working if any of the fields are null, you can
use the Nz function to convert a null to zero, as in this example:

STATUS:
IIf(Nz([RECD_QTY],0)=Nz([JUNKED],0)+Nz([RETAINED],0)+Nz([SURPLUS],0)+Nz([SENT_BACK],0),"OPEN","CLOSED")


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

In my Form, I have a field called "Status" which should say Open or Closed if
the value in field "Recd_Qty" = Junked+Retained+Surplus+Sent_Back, "Close",
if not equal "Open."

Is the following syntax correct, if not, please can you help me?

STATUS:
IIf([RECD_QTY]=[JUNKED]+[RETAINED]+[SURPLUS]+[SENT_BACK],"OPEN","CLOSED")