calculated field

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have 3 fields: LoadComp(an on/off checkbox
acknowledging when a product is loaded), QtyReqd(total
quantity required of a product), QtyBO(quantity
backordered if complete quantity not fulfilled).

I already have a calculated field that reports the
quantity backordered if LoadComp is not checked:
BackOrderQty: Iif([LoadComp],0,[QtyReqd]) - This works
well in the event that none of the required order are
fulfilled.

Is there an expression that would make the BackOrderQty
calculate if LoadComp is checked but some of the total
required are going to be backordered? Example:
LoadComp=On / QtyReqd=5 / QtyBO=1 ....Then BackOrderQty
should needs to show as 1.

Any help is appreciated! Thanks!
 
Kevin,

I might be missing something here, regarding your meaning, but does
this serve your purpose?...
IIf([LoadComp],[QtyBO],0)

- Steve Schapel, Microsoft Access MVP
 
Steve,
I believe that works if LoadComp is checked On, but
LoadComp may be On or Off, and that's where I run into
trouble. I can get it to work one way or the other, but
not in both scenarios. I had tried nesting Iif statements
to generate an either/or result. I attempted multiple
combinations of expressions with no results. I'm not in
full understanding of how or if nested Iif statements work.

Any thoughts or advice? Thanks!
Kevin
-----Original Message-----
Kevin,

I might be missing something here, regarding your meaning, but does
this serve your purpose?...
IIf([LoadComp],[QtyBO],0)

- Steve Schapel, Microsoft Access MVP


I have 3 fields: LoadComp(an on/off checkbox
acknowledging when a product is loaded), QtyReqd(total
quantity required of a product), QtyBO(quantity
backordered if complete quantity not fulfilled).

I already have a calculated field that reports the
quantity backordered if LoadComp is not checked:
BackOrderQty: Iif([LoadComp],0,[QtyReqd]) - This works
well in the event that none of the required order are
fulfilled.

Is there an expression that would make the BackOrderQty
calculate if LoadComp is checked but some of the total
required are going to be backordered? Example:
LoadComp=On / QtyReqd=5 / QtyBO=1 ....Then BackOrderQty
should needs to show as 1.

Any help is appreciated! Thanks!
Click to expand...

.
Click to expand...
 
Kevin,

I have obviously missed your meaning. Can you please try explaining
again what you want to happen if LoadComp is On and what you want to
happen if LoadComp is Off?

- Steve Schapel, Microsoft Access MVP
 
Steve,
If LoadComp is Off - I want calculated field BackOrderQty
to become the value in field QtyReqd. I have successfully
accomplished this using the expression:
Iif([LoadComp],0,[QtyReqd])

If LoadComp is On, but a value of greater than 0 is
entered in QtyBO - I want calculated field BackOrderQty to
become the value entered in field QtyBO.

I hope that makes it clear. The thing I have been unable
to achieve is having the calculated field work with
LoadComp being both on OR off.
Thanks for your continued attention to this! I look
forward to your response!
Kevin
 
Kevin,

Thanks for the further clarification.

How about when LoadComp is On, but QtyBO is 0?

- Steve Schapel, Microsoft Access MVP
 
Steve,
If LoadComp is On, and QtyBO is 0, BackOrderQty remains at
0. This is usually the case since our orders are usually
filled complete.
Kevin
-----Original Message-----
Kevin,

Thanks for the further clarification.

How about when LoadComp is On, but QtyBO is 0?

- Steve Schapel, Microsoft Access MVP


Steve,
If LoadComp is Off - I want calculated field BackOrderQty
to become the value in field QtyReqd. I have successfully
accomplished this using the expression:
Iif([LoadComp],0,[QtyReqd])

If LoadComp is On, but a value of greater than 0 is
entered in QtyBO - I want calculated field BackOrderQty to
become the value entered in field QtyBO.

I hope that makes it clear. The thing I have been unable
to achieve is having the calculated field work with
LoadComp being both on OR off.
Thanks for your continued attention to this! I look
forward to your response!
Kevin
Click to expand...

.
Click to expand...
 
Kevin,

Then I imagine this will give the required result...

BackOrderQty: IIf([LoadComp],[QtyBO],[QtyReqd])

- Steve Schapel, Microsoft Access MVP
 
Back
Top