IIF function qry run from 2 tables

  • Thread starter Thread starter KarenY
  • Start date Start date
K

KarenY

I can have the 'blank' field worked out into "0", but I can't have it with 2
tables.
Please help.

I have 2 tables :
field [PN] and [UnitPrice] in tbl 1
field [PN] and [ListPrice] in tbl 2

here's my function in the qry:
UnitList: (IIf([UnitPrice]=0,[ListPrice],[UnitPrice]))

I want the 0-priced PN in tbl 1 to pick up the price in tbl 2 if there is,
if no price in tbl 2, then back to the 0-price in tbl 1. Yet it doesn't
work, it returns as "blank" not "0", it's probably no [PN] in tbl 2.

thanks,
Karen
 
Hi Karen
Try: UnitList: (IIf([UnitPrice]=0,NZ([ListPrice]),[UnitPrice]))

That way if the query hits a null on table2 price you get a "0" instead of a
blank.

Try it

Kevin
 
Thank you, it works !
(sorry, a bit late to reply cuz of my leave...)

kc-mass said:
Hi Karen
Try: UnitList: (IIf([UnitPrice]=0,NZ([ListPrice]),[UnitPrice]))

That way if the query hits a null on table2 price you get a "0" instead of a
blank.

Try it

Kevin

KarenY said:
I can have the 'blank' field worked out into "0", but I can't have it with
2
tables.
Please help.

I have 2 tables :
field [PN] and [UnitPrice] in tbl 1
field [PN] and [ListPrice] in tbl 2

here's my function in the qry:
UnitList: (IIf([UnitPrice]=0,[ListPrice],[UnitPrice]))

I want the 0-priced PN in tbl 1 to pick up the price in tbl 2 if there is,
if no price in tbl 2, then back to the 0-price in tbl 1. Yet it doesn't
work, it returns as "blank" not "0", it's probably no [PN] in tbl 2.

thanks,
Karen
Click to expand...
Click to expand...
 
Back
Top