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..

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

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

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 :D. Thanks for the help and next time im working so late and can't figure the problem out I will just sleep on it :)