A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Simple Query Expression



 
 
Thread Tools Display Modes
  #1  
Old October 7th, 2009, 08:21 PM posted to microsoft.public.access.queries
Agent_KGB
external usenet poster
 
Posts: 1
Default 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  
Old October 7th, 2009, 08:32 PM posted to microsoft.public.access.queries
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default 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  
Old October 7th, 2009, 08:57 PM posted to microsoft.public.access.queries
Agent_KGB[_2_]
external usenet poster
 
Posts: 29
Default 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  
Old October 7th, 2009, 09:50 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old October 7th, 2009, 10:03 PM posted to microsoft.public.access.queries
Agent_KGB[_2_]
external usenet poster
 
Posts: 29
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.