IsError expression producing #Type! error on report

[Cori]

I have an Iif(isError) formula that normally works great but on a new report I'm getting some results with a #Type! error. I believe I'm getting the error because the second field in my formula is blank but I don't know how to correct my formula.

My current Formula: =IIf(IsError([BBBM].[Text]),"",[BBBM].[Text] & " (" & Replace([BBBASTMN].[Text],","," or") & ")")

For example, if BBBM is blank then the normal output for the row is empty and hidden. If BBBM = ITCS and BBBASTMN (a multi-select field) = A352,A216 then the normal output on the report is ITCS (A352 or A216) but if the field BBBASTMN is empty the output should be just ITCS but instead I'm getting #Type!.

I can't figure out how to adjust the formula to show the first field if the second field is blank. Any assistance is greatly appreciated.



Thanks,
Cori

[June7]

Text is a reserved word. Should not use reserved words as names.

Replace() will error if field is null. Could handle possible null with Nz() or Is Null - review http://allenbrowne.com/QueryPerfIssue.html#Nz

Replace will not work on a multi-value field because the data is not a string. I NEVER use multi-value fields. https://support.office.com/en-us/art...C-6DE9BEBBEC31

[Cori]

June7,

At this point I can't replace every multi-value field within the database. I'm inheriting a database where hundreds of tables, queries and forms have already been created, I'm just creating the few reports that hadn't been done earlier. So far the replace has been working fine but looking at the first link, do I need to somehow add a nested IsNull with the Replace?

Thanks so much,
Cori

[June7]

Is the field a multi-value? If so, Replace() will not work because the data is not a string.

I don't see how it could have 'been working fine' if used on multi-value fields.

[Cori]

June7,

Sorry, I may not be using the correct terminology. On previous reports the replace() worked fine as long as there was data in both fields (these two "fields" are lookup from other two tables). If I'm looking at the description correctly, the original table (for the secondary field) has a data type of short text. The main table that pulls these two tables in (and which all queries and therefore reports are based on) uses a lookup that "allows multiple values" and has a data type = number. I've inherited a complex monster that will be used as-is for the near future. Hope this makes sense.

Thanks.

[June7]

That means a multi-value field. Seems you need a better understanding of what they are: https://support.office.com/en-us/art...C-6DE9BEBBEC31

And then if you still want the descriptive text concatenated with " or " connector, review: http://allenbrowne.com/func-concat.html

I still don't understand how the Replace function was working before.