Iif Function in MakeTable

[Rich_Lovina]

I have two types of titles, some yield a rank and others "May be Displayed".
My related table Poscode has DisplayTitle, yes/no.

I want to be able to set the criteria such that :

IIf([Poscode]![DisplayTitle]=Yes,[Poscode]![Title],[Poscode]![Title]=" "

but this function always runs only yielding records meeting the 1st criteria. viz. it is ignoring selection of the set whose titles I DON'T wish to see, and displaying the field for them as blank.

What simnple thing am I missing?

 

[Rich_Lovina]

Further thoughts on this, perhaps creating a calculated new field to work with the Iif will be the better way to go??

 

[Rich]

I'm not sure I understand what your trying to do but your Iif statement is constructed to show blank records when the condition is false, if you do not want to see blanks, remove the Iif statement and set the criteria for [DisplayTitle]to yes.
HTH

 

[3divine]

Also keep in mind that in Access,

"" = NULL and Access returns no value

" " = Space

Another thing. IIf function will display the result as soon as the first condition is true regardless of what the false condition is

 

[Rich_Lovina]

Thanks all for your input, but the problem is that the Iif is supposed to show me the blanks as well, but it's not! It is placed in the Criteria line, but is yielding only 91 records (DisplayTitle=yes)when it is supposed to show all 152 records incl. the blank title field.

In other words, as the second commentator has added, the Iif is only working on the 1st argument? Why?

 

[Rich_Lovina]

More lateral play just solved this problem, but it appears the Iif function doesn't do what its supposed to.
Instead, I created a new field, MAILTITLE, then used UPDATE set as DisplayTitle=No, used " " as the update and got the result. Then used copy and paste from select query to add the titles which I want displayed.
I think that Iif is a bit Iffy?

 

[Rich_Lovina]

Thanks Pat, discovered that but always thought that Iif returned 2 options both true condition and false condition. In the process, found Copy & Pasting from a SELECT helped establish the result in the existing table in a new field, without needing to constantly make new tables, change relationships, data definitions, etc.