IIF Statement with Yes/No Field

[Valery]

Hi,

I am trying to write a statement and it returns: #Type!. I know what this means but it should not give this error.

Here is what I am trying to do. I have button that copies a certain number of fields to the clipboard. The one below is one of them.

Here is the coding I am using that works:

=Trim(IIf([CallingContactRel] Is Not Null,[CallingContactRel] & ": " & [CallingContact],[CallingContact]))

I would like to add the following condition to this code.

I have a yes/no field called BookingNoteContactExcl. If this is ticked, it should exclude [CallingContact] from being copied, in other words, return "" in the code above.

Can you help? THANK YOU!

 

[James Dudden]

Here is everything you need broken down. You can simplify if you like but you get the idea:

IIf(Not Isnull([CallingContactRel]),[CallingContactRel],"") &
IIf(Not Isnull([CallingContactRel]) and nz([BookingNoteContactExcl],0), ": " ,"") &
IIf(nz([BookingNoteContactExcl],0),[CallingContact],"")

 

[Valery]

Wow! This coding is amazing! I did not know you could have several "IIf" into one code in a text box.

I can't figure out where to edit it though even though it looks so clear!

Working fine when box is not ticked: Copying CallingContactRel + colon + CallingContactRel.

However, when the box is ticked (BookingNoteContactExcl) it is still copying the field: CallingContactRel.

Sorry - I should be able to figure this out - I think! I played with it and I am making it worse, LOL

THANK YOU!@!

 

[James Dudden]

Sorry, the correct line should be this:

=IIf(Not Isnull([CallingContactRel]),[CallingContactRel],"") & IIf(Not Isnull([CallingContactRel]) and not nz([BookingNoteContactExcl],0), ": " ,"") & IIf(not nz([BookingNoteContactExcl],0),[CallingContact],"")

 

[Valery]

Still showing the relationship... Example
With the box ticked off: xxx daughter xxx
Without the box ticked off: xxx daughter: Jane Doe xxx (which is right)

Am I doing something wrong maybe?

Stupid question perhaps but shouldn't one of the BookingNoteContactExcl be 1 and not 0... (please ignore if silly)

Everything I try on my own - even inspired by your coding - returns this : #Type!

 

[James Dudden]

The syntax above is correct if your Yes/No field is a traditional Yes/No field as the values it uses are -1 for True and 0 for false. If you have created your own Yes/No field maybe in a drop-down then the values could be anything and the syntax will be wrong.

 

[Valery]

No it is a Yes/No field... let me double check... Thank you for your patience.

 

[Valery]

Just checked - it is a traditional Yes/No field - Says in properties "True/False" with 0 as default value.

Just tried the code again and still getting the Contact's Relationship in the copy

Wait -though of something: does it matter that the field is invisible - the one with your coding in it?

 

[Valery]

I have reduce the database to a simple form that includes this issue. Information is on the form, using labels.

Maybe this will help.

THANK YOU