IIF Query Problem

[crhodus]

I'm using the following query to populate a temporary table:

INSERT INTO tmpMailMerge ( project_id, comp_name, company_prefix, company_contact_name, mail_add_1, mail_add_2, mail_city, mail_state, mail_zip, mail_country )
SELECT project.project_id, Company_Info.comp_name, Contact_Info.company_prefix, Contact_Info.company_contact_name, Contact_Info.mail_add_1, Contact_Info.mail_add_2, Contact_Info.mail_city, Contact_Info.mail_state, Contact_Info.mail_zip, (iif( [Contact_info.mail_country] ="USA", "",Contact_info.mail_country)) AS mail_country
FROM (project RIGHT JOIN Company_Info ON project.project_id = Company_Info.project_id) RIGHT JOIN Contact_Info ON Company_Info.company_id = Contact_Info.company_id;

If the contact_info.mail_country value for a record is USA, then I don't want anything inserted into tmpMailMerge.mail_country. If the Contact_info.mail_country value for a record is something other than USA then I want that country name to be inserted into tmpMailMerge.mail_country.

My query populates table tmpMailMerge correctly except for the mail_country column. Nothing appears in this column. Can anyone tell me what I'm doing wrong?

Thanks!
crhodus

 

[Jon K]

By default, the "Allow Zero Length" property of a text field is set to No and so it won't accept "".


Try changing "" to Null in the IIF expression i.e.

(iif( [Contact_info.mail_country] ="USA", Null, Contact_info.mail_country)) AS mail_country

 

[RichO]

Did you try:

(IIf( [Contact_info.mail_country] ="USA", "",Contact_info.mail_country)) AS Expr1

 

[crhodus]

Jon K,

I replaced the double quotes with Null and this seems to have corrected the problem.

Thanks for your help!

crhodus