Thanks, it sort of works... now i get my custom message for ALL records with
"flag" field, no matter if there is anything in notes field or not

here is my expression:
New Flag: IIf(Not (IsNull([boxid])) And [clmflag]="G","G with PO",[clmflag])

[boxid] = "Notes" field
[clmflag] = "Flag" field

and here is the SQL

SELECT [FEED Master].Account, Count([FEED Master].Account) AS
CountOfAccount, Max([tbl_Bad Address List].clmflag) AS MaxOfclmflag,
Max([tbl_Bad Address List].boxid) AS MaxOfboxid, IIf(Not (IsNull([boxid]))
And [clmflag]="G","G with PO",[clmflag]) AS [New Flag]
FROM [FEED Master] LEFT JOIN [tbl_Bad Address List] ON [FEED Master].Mobile
= [tbl_Bad Address List].phone
GROUP BY [FEED Master].Account, IIf(Not (IsNull([boxid])) And
[clmflag]="G","G with PO",[clmflag])
ORDER BY [FEED Master].Account, Count([FEED Master].Account) DESC;

any idea what i am doing wrong?

"Piet Linden" wrote:

On Oct 7, 2:21 pm, Agent_KGB
wrote:
Hello, i am trying to build an expression that will compare two fields and
will either create new value or copy current value from one of the fields....

I have two fields in my query: "Flag" (Red, Blue, Green, etc) "Notes" (text
field)

What i want is to check both fields and anytime there is a record with "Red"
flag and something in "Notes" field it will put "Red Flag with Notes" in
expression field, and for all other records it will simply copy the value
from "Flag" field.

I am sure it's a simple query, i just wish i'd knew how to write it.

IIF(Not(IsNull([Notes])) AND [Flag] = "Red", "Red Flag with Notes",
[Flag])