IIF statement the correct one to use?

[theborgking1]

I have an issue which I need help with

I am trying to get access to do the following

If [column 1] is blank then use the data out of [column 3] and shoved it into [Column 4]

can anyone help?

many thanks

 

[pr2-eugin]

Hello thebogking1, Welcome to AWF..

I think a simple Nz function should do the trick..

Column4: Nz(Column1, Column3)

 

[theborgking1]

Thank you so much for the reply. The issue I have is that I am trying to fix somebody elses db which they created who has now left

They use the IIF statement

for example

& IIf(Not IsNull([PAON_START_NUM]),[PAON_START_NUM],"") & IIf(Not IsNull([PAON_START_SUFFIX]),[PAON_START_SUFFIX] & " ")

I have been told I need to use this to achieve what is required.

Can you explain exactly what they were saying in the above statement please? :banghead:

 

[pr2-eugin]

Okay IIF - Immediate IF is just a simplified If statement.. It takes in three arguments.. The first is the Condition to check for, second is the Value if the Condition evaluates to be true, and finally the Value if the condition fails..

IIF ( [I][B]conditionToCheck [/B][/I], [I][B]valueIfTrue [/B][/I],[I][B] valueIfFalse [/B][/I])

So the condition you are checking here is is the field [PAON_START_NUM] Is NOT Null..

[COLOR=Green][B]Not[/B][/COLOR] [B][URL="http://www.techonthenet.com/access/functions/advanced/isnull.php"][COLOR=Magenta]IsNull([/COLOR][/URL][COLOR=DarkOrange][PAON_START_NUM][/COLOR][/B][COLOR=Magenta][B])[/B][/COLOR]

If it is Not Null then the value is to be placed, else an empty string should be placed.. The Nz() function is a Simplified IIF that checks for only one Condition Is Null.. So.. Technically..

IIf(Not IsNull([PAON_START_NUM]),[PAON_START_NUM],"")
[COLOR=Green][B]'is same as [/B][/COLOR]
Nz([PAON_START_NUM]),"")

Tested,

? IIf(Not IsNull(Null),"The Value Is Not Null","The Value Is Null")
The Value Is Null
? IIF(Not IsNull("The Value Is Not Null"),"The Value Is Not Null","The Value Is Null")
The Value Is Not Null
? Nz(Null,"The Value Is Null")
The Value Is Null
? Nz("Not Null","The Value Is Not Null")
Not Null

Whatever they say can also be achieved by Nz..

 

[theborgking1]

Paul,

Once again thanks

So what I need to say now is this...

If there is an empty value/null value in the [PAON_START_NUM] then use the value that is in [PAON_TEXT])

I amatuerishly thought it would be this....

(IIf(IsNull([PAON_START_NUM]),([PAON_START_NUM]), & "-" "") & (IIf(Not IsNull([PAON_TEXT],[PAON,TEXT] & " " "")

shows i have a long long way to go

Any ideas?

 

[pr2-eugin]

Have you given a shot at trying any of the suggestion I have made so far??

Nz([PAON_START_NUM],[PAON_TEXT])

 

[theborgking1]

Paul you are a star. Tried the NZ then got rid of the IIF's shortened my statement considerably

Thank you so so much

 

[pr2-eugin]

You are welcome..

Just that you know my suggestion on Post#2 was the same as Post#6.. Except you needed to change Column1 to [PAON_START_NUM] and Column3 to [PAON_TEXT]