IIF for changed fields that excludes if both fields are empty or null (1 Viewer)

kb44 · Feb 5, 2019

Hi all,

I need to build an expression that shows a 1 if the fields have changed (not if they are not both null or have if they both an empty space in them).

Meaning, if the fields are empty, I do not want the field to return a 1. I have the IIF statement, but can't figure out where to put the null or the "" parts.

Here is what I have so far:

Changed_Corp_Job_Y_N: IIf([1-b Monthly_Staffing].[Corporate Job Title]=[tbl_Select Current Month].[Corporate Job Title],0,1)

Thanks!

theDBguy · Feb 5, 2019

Maybe something like?

Code:

IIf(Field1=Nz(Field2,""),0,1)

kb44 · Feb 5, 2019

theDBguy said:
Maybe something like?

Code:

IIf(Field1=Nz(Field2,""),0,1)

This is how I'm interpreting it:

Changed_Country_Y_N: IIf([1-b Monthly_Staffing].[Country]=Nz([tbl_Select Current Month].[Country],""),0,1)

With this I get 640 true out of 7xx. Changes should be around a hundred.

Maybe it's reversed?

theDBguy · Feb 5, 2019

Hi. I just followed your example. Looking back at it, yes, I think you should switch it around. Try it out and let us know.

kb44 · Feb 6, 2019

Hello again,

There is a twist. Sometimes the field change will be an empty field. Meaning that the user will delete the field contents and leave it blank.

I want this change to be noted if the field had something in it to begin with but not if it was already empty (since that would mean there is no change).

Any idea how to accomplish this?

The_Doc_Man · Feb 6, 2019

You have introduced a compound computation into the mix. Expect to need more than one IIF statement embedded in this expression. I.e. nested IIFs. Perhaps you could create a little contrived set of samples to demonstrate what you would expect to see for each of several possible input conditions, we might be able to develop a truth table for you that would help organize the result.

Code:

Title1B   TitleCur   Changed
Clerk I    Clerk I     0
Clerk I    Clerk II    1
null        Clerk I     ?    <== show us what you would WANT to see...
Clerk I    null         ?
Acct III  Acct II   1
Boss I    blank      ?
...

As an aside, you have some long and complex names there, including special characters and embedded spaces. Access can handle that, but it is a pain in the toches to have to type in all of those brackets because of the complex names. But that's for later consideration.

kb44 · Feb 6, 2019

The_Doc_Man said:
You have introduced a compound computation into the mix. Expect to need more than one IIF statement embedded in this expression. I.e. nested IIFs. Perhaps you could create a little contrived set of samples to demonstrate what you would expect to see for each of several possible input conditions, we might be able to develop a truth table for you that would help organize the result.

Code:

Title1B TitleCur Changed Clerk I Clerk I 0 Clerk I Clerk II 1 null Clerk I ? <== show us what you would WANT to see... Clerk I null ? Acct III Acct II 1 Boss I blank ? ...

As an aside, you have some long and complex names there, including special characters and embedded spaces. Access can handle that, but it is a pain in the toches to have to type in all of those brackets because of the complex names. But that's for later consideration.

If anything of the following change I want to display a 1:

If there was input in the before field, but it was deleted in the after table
and now there is a blank space
If there was a blank space in the before field but now there are characters in the after field
If the text has changed

One of the main issues right now is that when there is a blank space in the "before changes" field and also in the "changed" field it's still noted as a change.

PS I will address the long names in the future. Thank you.

theDBguy · Feb 6, 2019

Hi. Not sure if this will help but try:

Code:

IIf(Nz(Field1,"")=Nz(Field2,""),1,0)

kb44 · Feb 6, 2019

theDBguy said:
Hi. Not sure if this will help but try:

Code:

IIf(Nz(Field1,"")=Nz(Field2,""),1,0)

This worked! Thanks for your help. Much appreciated!

theDBguy · Feb 6, 2019

Hi. You're welcome. Glad to hear you got it to work. Cheers!

isladogs · Feb 6, 2019

Hold on.
That expression will be 1 if both fields are null or empty strings.
Reading post #1, I believe those conditions should result in 0.

I need to build an expression that shows a 1 if the fields have changed (not if they are not both null or have if they both an empty space in them).

Meaning, if the fields are empty, I do not want the field to return a 1.

The_Doc_Man · Feb 7, 2019

The expression was written backwards? We are looking for changes so the rough attempt should have used a <> type of operator.

Code:

IIF ( Nz( [Field1], "" )  <>  Nz( [Field2], "" ), 1, 0 )

This should work as follows:

1. Field 1 empty, Field 2 NOT empty --> 1
2. Field 1 NOT empty, Field 2 empty --> 1
3. Field 1 NOT empty AND field 2 NOT empty AND field 1 <> field 2 --> 1
4. Field 1 empty AND field 2 empty --> 0
5. Field 1 NOT empty AND field 2 NOT empty AND field 1 = field 2 --> 0

isladogs · Feb 7, 2019

Provided this rather convoluted sentence means what I think it does:

I need to build an expression that shows a 1 if the fields have changed (not if they are not both null or have if they both an empty space in them).

then either of these will work
a) reverse the 0,1 arguments

Code:

IIF(Nz([Field1],"")  =  Nz([Field2],""), 0, 1 )

b) change to <> as Doc said

Code:

IIF ( Nz( [Field1], "" )  <>  Nz( [Field2], "" ), 1, 0 )

Both produce EXACTLY the same results which are (I believe) what the OP wants

However if the OP believes that the expression is post #9 is correct, then he must mean something else in his request!

kb44 · Feb 7, 2019

isladogs said:
Provided this rather convoluted sentence means what I think it does:

then either of these will work
a) reverse the 0,1 arguments

Code:

IIF(Nz([Field1],"") = Nz([Field2],""), 0, 1 )

b) change to <> as Doc said

Code:

IIF ( Nz( [Field1], "" ) <> Nz( [Field2], "" ), 1, 0 )

Both produce EXACTLY the same results which are (I believe) what the OP wants

However if the OP believes that the expression is post #9 is correct, then he must mean something else in his request!

My apologies for the confusion guys.

Both tables have the same fields.

A change can mean any change between the old and new files.

Including deleting of a field. But if the old and new tables both show a blank space, I do not want to show that as a change (since it isn't).

That was one of the main problems I was having.

Needless to say, I was leaving work yesterday and looked at the wrong file when I said it worked. It did not. I will try the ones you guys posted now.

Thanks.

kb44 · Feb 7, 2019

isladogs said:
Provided this rather convoluted sentence means what I think it does:

then either of these will work
a) reverse the 0,1 arguments

Code:

IIF(Nz([Field1],"") = Nz([Field2],""), 0, 1 )

b) change to <> as Doc said

Code:

IIF ( Nz( [Field1], "" ) <> Nz( [Field2], "" ), 1, 0 )

Both produce EXACTLY the same results which are (I believe) what the OP wants

However if the OP believes that the expression is post #9 is correct, then he must mean something else in his request!

In the above sql does:

field1 = table with new changes?
field2 = table without new changes?

does that even matter?

Also, when I use <> Access does not let me go back to design view. Only SQL view because it says Design view can't interpret <>. So I would prefer using = if possible.

kb44 · Feb 7, 2019

Definitely working now guys. Just had to flip the 1 and 0 as you said. Thanks again!

isladogs · Feb 7, 2019

kb44 said:
Also, when I use <> Access does not let me go back to design view. Only SQL view because it says Design view can't interpret <>. So I would prefer using = if possible.

That is the case if you used <> to 'connect' the tables.
If you were using an expression as a query field or as filter criteria, either would have been OK in design view.

Anyway, glad its now working for you

kb44 · Feb 7, 2019

isladogs said:
That is the case if you used <> to 'connect' the tables.
If you were using an expression as a query field or as filter criteria, either would have been OK in design view.

Anyway, glad its now working for you

Got it. I will remember that.

IIF for changed fields that excludes if both fields are empty or null (1 Viewer)

kb44

Registered User.

theDBguy

I’m here to help

kb44

Registered User.

theDBguy

I’m here to help

kb44

Registered User.

The_Doc_Man

Immoderate Moderator

kb44

Registered User.

theDBguy

I’m here to help

kb44

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

kb44

Registered User.

kb44

Registered User.

kb44

Registered User.

isladogs

MVP / VIP

kb44

Registered User.

Similar threads

Users who are viewing this thread