If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Simple Query Expression
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. |
#2
|
|||
|
|||
Simple Query Expression
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]) |
#3
|
|||
|
|||
Simple Query Expression
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]) |
#4
|
|||
|
|||
Simple Query Expression
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 Agent_KGB wrote: 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]) |
#5
|
|||
|
|||
Simple Query Expression
Thanks John, worked like a charm!!!
"John Spencer" wrote: 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 Agent_KGB wrote: 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]) |
Thread Tools | |
Display Modes | |
|
|