IIF Statement to replace " "

[Cobbdj]

Hello,

I am trying to populate " " with the following script in Access:



Expr1: IIf([dbo_CMC_CLCL_CLAIM].[CLST_MCTR_REAS]=" ",[CER_SYML_MSG_LOG].[SYMD_MSG_CD],[dbo_CMC_CLCL_CLAIM].[CLST_MCTR_REAS])

However, it is not showing the "SYMD_MSG_CD", but another code all together. Can someone give me some direction?

[June7]

That looks like a space within quote marks. If you want to check against empty string, don't use space.

Do you allow empty strings in table? I don't. I look for null.

Expr1: IIf(IsNull([dbo_CMC_CLCL_CLAIM].[CLST_MCTR_REAS]),[CER_SYML_MSG_LOG].[SYMD_MSG_CD],[dbo_CMC_CLCL_CLAIM].[CLST_MCTR_REAS])

Is there more than one field in the query with same name? If not, don't need the table prefix.

If you want to handle possibility of either null or "":

Expr1: IIf([dbo_CMC_CLCL_CLAIM].[CLST_MCTR_REAS] & "" = "", [CER_SYML_MSG_LOG].[SYMD_MSG_CD], [dbo_CMC_CLCL_CLAIM].[CLST_MCTR_REAS])

[Cobbdj]

Actually, this database contains " " (padded spaces). I will try running the suggested script you provided. hopefully, that generates the correct results..... I have been working on this for a week.....Ugh!

Thanks,

Dina

[Cobbdj]

That worked! Thanks a bunch!