Append Query with IIF statement

[Cheshire101]

Thank you in advance for help given,

I am trying to run an append query - I need to be able to update the items to another table based on weather a field contains the information to match that field.



Problem: There are multiple rows of data with True/ False combinations, in others words just because it is true for one does not mean it false for all others.

IIf([Forms]![MainForm]![Field]="Name",True,"")

This works if it is true only. I am sure the "" needs to be something else just can not figure out. I want it to have no criteria if the IIF statement is false.
I have tried Like "*" and "*"
BTW - It can't be False because the item might be true for other columns.

[ajetrumpet]

First of all, you have to realize that boolean fields cannot contain a NULL value. It is either true or false, nothing else (unless I'm too tired to think). Now that I think about it though, I'm not sure what it returns if you delete out the default value of FALSE in the table field properties. I know it's not NULL though. So...if you're appending records that are going to contain these types of fields, what you're asking is impossible and might actually throw an error if you try.

[NoellaG]

Hi,

YES/NO fields can contain a null value. It just means you don't know if it's true or not, so normally

IIf([Forms]![MainForm]![Field]="Name",True,Null)
would have to work.

grNG

[ajetrumpet]

Hi,

YES/NO fields can contain a null value. It just means you don't know if it's true or not, so normally

IIf([Forms]![MainForm]![Field]="Name",True,Null)
would have to work.

grNG

I'd love to see proof of this. I disagree.

[weekend00]

I tried in Access 2003, but could not set to null. only 0 or -1 can stay.

[NoellaG]

Hi,

I used the triple state in previous versions of Access, but checking those with Access 2010 there seems to be a problem now. Once I set the triple state to "yes" I don't seem to be able to edit the check box anymore, which could pose aproblem if anyone of the older customers upgrades its app. Using Access help they still talk about the null value of the boolean field though (see attachment) ....


grNG