|
Subject:
|
calculated control box with iff
|
|
Posted By:
|
ultimateace
|
Post Date:
|
1/20/2007 1:13:03 AM
|
I am having problems with a iff statement.
I need to display a value from a table not connected to the form in control box. (brandname) based on the another textbox (brandid) the brandname is stored in a seperate table from the id that i will be using to reference it. but the complicated part is there is a chance that there may be no brandid. my solution was this
in the control source... =iff(isnull(brandid),"no brand",dlookup("brandname","brand","[brandid] = "& brandid.value))
but it doesn't work
the dlookup part works except where there is no brandid then there is an error message. i just need the no brand to appear when no brand is given... other than having a default value set to no brand on the table.... that would complicate much more..
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/22/2007 7:31:25 AM
|
This is a design issue (I spelled it right that time!)
You should have a Brand table:
tblBrand
BrandID - PK
BrandName - Text
Then use the combo box to select the BrandName (since that is the significant value) and the table will store the BrandID. When you need the BrandName in a report, it will automatically be there.
There is NO case where you should have a BrandName without a BrandID.
HTH
mmcdonal
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
1/23/2007 4:54:29 PM
|
MMcDonal is correct. If BrandID is the primary key, it should never be null. If BrandID is not the primary key, then this may work; however, one questions why you're not searching using a primary key.
=Nz(DLookup("[BrandName]","Brand","[BrandID] = " & BrandID.Value), "No Brand")
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
ultimateace
|
Reply Date:
|
1/23/2007 7:40:16 PM
|
Thank you,
And it was a stupid error sorry.. after posting it i figured it out. It was in fact what you said it is always usefull to use the iif statement instead of the iff statement  . Thanks for the help and next time im working so late and can't figure the problem out I will just sleep on it :)
|
