Access IFF Statments not working

[Martano0811]

Sorry of this is elementary to everyone but I can't seem to get this statement to generate N/A if both fields from the tables are Null. Here is the Criteria I wrote:


IIf(IsNull([CPC_Old].[Project released for planning Actual]) & IsNull([CPC_New].[Project released for planning Actual]),"","N/A") & IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes")


The output Im getting is Yes if they both fields match, No is they are different...this is working...but it appears that if both fields are null then it defaults to Yes.

Any help here?

 

[pr2-eugin]

ALREADY POSTED IN YOUR PREVIOUS POST:

Martano0811, I would love to help but I really need some examples or some cases which will show what your desired output should be.. in simple english.. as spikepl has mentioned it really does not make any sense at all.. you are trying to match between a String and a Boolean, which CANNOT BE DONE AT ALL..

Why do you want YES/NO now?

Well based on my perception, what you are trying to do is:

* Check if [CPC_Old].[Project released for planning Actual] AND [CPC_New].[Project released for planning Actual] are NULL, if so then enter the value N/A
* If either of the two fields are not empty then you have to put Yes or No.

Is that correct?

 

[Martano0811]

In english: Im comparing Dates from old to new table with alike fields....Im creating a Flag in the output to give me a Yes if the dates have changed and a No if they are the same...now if both fields are empty then I need an N/A . Ive already got a Yes and No in the output ...I simply need a third varialbe output to give me an N/A. This has to be possible and I cant figure it out nor are there example out their on the web.

How do I keep it all String or All Boolean If i cant match between the two.

 

[Martano0811]

Well based on my perception, what you are trying to do is:

* Check if [CPC_Old].[Project released for planning Actual] AND [CPC_New].[Project released for planning Actual] are NULL, if so then enter the value N/A
* If either of the two fields are not empty then you have to put Yes or No.

Is that correct?
__________________


Thats sounds correct - yes.

 

[pr2-eugin]

Try this..

IIf(IsNull([CPC_Old].[Project released for planning Actual]) AND IsNull([CPC_New].[Project released for planning Actual]),"N/A",IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes"))



It is called Nesting of IIF.. it works like.


IIF(Check_condition, true_part, IIF(Check_another_Condition, true_part, false_part))

 

[Martano0811]

Yes, this seems to work but the output is ommiting the N/A's meainging its only showing Yes and No population not the N/A population. How do I keep the N/A polustion displayed? Thank you very much.