Switching True/False Value for Report

[Montez659]

The title seems simple enough, and I know what you are thinking, "Just format the textbox as Yes/No or whatever." However, what I am wanting seems a bit more complicated. I am working with a db that was not created by me so I have to work within some bounds.

This is one of them.

The data field is a checkbox that is checked if "No Errors" are present. However, in my report, I want that to display as "Yes/No" with a title of "Errors Present." The way it is structured, if there a box checked (no error) it would display as "Yes" on the report, giving the exact opposite of what it should be.

Soooo...anyway to switch Yes/No to No/Yes for a True/False scenario (i.e., True = No instead of Yes)?

Thanks for the help as always.

 

[Montez659]

Why is it that I always find the answer within minutes of posting when I spent hours before looking? You just gotta know what to search for.

Anyways, this post http://www.mrexcel.com/forum/showthread.php?t=137342 led me to my solution of being able to custom format, with my final code looking like:

=IIf([No Errors]=True,"No","Yes")

 

[Norie]

You don't say what type of field it is, or what the source for the report is.

There are few methods you could use, for example in a query an expression like this.

YesNo:Iif([FieldName], "No", "Yes")

Though you could dispense with the label in the report and use something like this.

ErrorsReported:Iif([FieldName], "Errors reported", "No errors reported")

I think I might have got the logic mixed up, but I hope you get the idea.

PS You could use these expressions in the report itself if you the source was a table, but using a query as a source can give you a lot more options.

 

[Montez659]

Norie, thanks for the extra info! The field is a textbox and the source is a query. I like your idea of dropping the label and using "Errors Reported" and "No Errors Reported." Great suggestions.

 

[Darren Bartrup]

Not sure if it's quicker (probably negligible), rather than using IIF I've started using

CHOOSE([FieldName]+2,'Yes', 'No')

 

[Norie]

Darren

How about this?

Format([TheField],""""";""No"";""Yes""")

Don't think I'd rely on it outside Access, or even inside Access for that matter.

 

[Darren Bartrup]

ok, you've done my head in now at 5pm.

Was looking at it for ages before realising it's just a custom number format.
Can see now why you wouldn't trust it as any negative number is 'Yes' and any positive number is blank.

Least with CHOOSE anything other than -1 and 0 will return an error.

Or you could always use:

YESNO: IIf(NZ([TheField],False)=True,'No',IIf(Not NZ([TheField],False)=True,'Yes',IIf(IsNull(NZ([TheField],False)),Null,Null)))

Double & triple checked for NULL in a boolean field just in case - it is Access after all.