Long shot but have tried using single quotes for 'COLLECTION' ?

Yes, I have tried using single quotes...:(

You are looking to add the Memo Text field to the report but only for invoices that have MEMO Code equal COLLECTION?

Correct!

The report will still pull all Memo Texts from all Memo Codes.

SELECT DISTINCT [AR_InvoiceHistoryMemo].[MemoText], [AR_InvoiceHistoryMemo].[MemoCode] FROM [AR_InvoiceHistoryMemo] WHERE [AR_InvoiceHistoryMemo].[MemoCode] = 'COLLECTION'

works for me.  are you getting an error message or no results?

Create a new SQL Data Expression call it Collection and add it to the report Columns.

Use the following SQL in the Expression Source:

CASE

WHEN AR_InvoiceHistoryMemo.MemoCode = 'COLLECTION' Then AR_InvoiceHistoryMemo.MemoText

ELSE

' '

END

The field will display the Memo Text for the Collection records and blank for everything else.

Hey! I'm still struggling with the same issue after three years, LOL, but I can clarify this. One invoice number could have multiple memo codes, and I want to add the memo text when the memo code = 'COLLECTION' only. Otherwise, different codes will duplicate rows/invoices in the report. Will your "case when" method still duplicate rows when there is more than one memo code? Thanks!

Hi, HABIB; it's been a while, LOL. It didn't work for me. Other memo codes still show up and duplicate the same invoice number. Thanks!