Simple Query Expression

[Agent_KGB]

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.

 

[Piet Linden]

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])

 

[Agent_KGB]

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?

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])

 

[John Spencer]

Try this variation which tests for NULL and zero-length strings.

IIF([Notes] & "" <> "" AND [Flag]="Red","Red Flag with Notes",[Flag])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

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?

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])

 

[Agent_KGB]

Thanks John, worked like a charm!!!

Try this variation which tests for NULL and zero-length strings.

IIF([Notes] & "" <> "" AND [Flag]="Red","Red Flag with Notes",[Flag])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

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?

On Oct 7, 2:21 pm, Agent_KGB <[email protected]>
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])