Form to display True/False as Yes/No

[Number11]

How do i get a form to show False as Yes/No

Tried with a query but didnt work is showing every record as YES

Order_Sent: IIf([Dispatch],"Yes","No")

 

[arnelgp]

you create a Query from your table:

select field1, iif([dispatch], "Yes", "No") As Order_Sent from yourTable

then use it in your form as recordsource.

 

[CJ_London]

or set the format property to yes/no in your despatch control

 

[GPGeorge]

How do i get a form to show False as Yes/No

Tried with a query but didnt work is showing every record as YES

Order_Sent: IIf([Dispatch],"Yes","No")

As a matter of fact, the datatype of underlying value is neither "true/false nor "yes/no". What you see displayed, i.e. "true" or "yes" is the Format applied to it for the purpose of displaying a more human friendly term.

In Access -- and nearly all other databases-- the stored datatype value for "false" is 0. In Access -- but in no other database that I know of -- the stored datatype value for "true" is -1. It's 1 in SQL Server and other databases.

So, the issue here is formatting for display, and that's subtly different. You want to FORMAT the value of "[Dispatch]" as a string for display. The 0 would be displayed as two characters "No", while the -1 would be displayed as three, "Yes". I get that the difference is not all that obvious because we're so used to just seeing "Yes/No". So, as a couple of people have suggested, you can apply formatting either in the query or in the control on the form.
I'd do it in the form in preference to the query because that allows the underlying value to be retained, i.e. -1/0 if it's needed otherwise. Formatting the control for display, on the other hand, only impacts what the user sees in the displayed value.

All of that said, the exact problem you have seems to suggest the REAL problem might not even be formatting at all.

How about this as a test.

IIf([Dispatch] = -1 ,"Yes","No")
or
IIf([Dispatch] = True,"Yes","No") Note: True is the boolean, "True" would be the text string to DISPLAY it for the human eye.

That will force the evaluation to consider the underlying datatype value, and might show you that something else is in that field other than a boolean.

 

[Number11]

As a matter of fact, the datatype of underlying value is neither "true/false nor "yes/no". What you see displayed, i.e. "true" or "yes" is the Format applied to it for the purpose of displaying a more human friendly term.

In Access -- and nearly all other databases-- the stored datatype value for "false" is 0. In Access -- but in no other database that I know of -- the stored datatype value for "true" is -1. It's 1 in SQL Server and other databases.

So, the issue here is formatting for display, and that's subtly different. You want to FORMAT the value of "[Dispatch]" as a string for display. The 0 would be displayed as two characters "No", while the -1 would be displayed as three, "Yes". I get that the difference is not all that obvious because we're so used to just seeing "Yes/No". So, as a couple of people have suggested, you can apply formatting either in the query or in the control on the form.
I'd do it in the form in preference to the query because that allows the underlying value to be retained, i.e. -1/0 if it's needed otherwise. Formatting the control for display, on the other hand, only impacts what the user sees in the displayed value.

All of that said, the exact problem you have seems to suggest the REAL problem might not even be formatting at all.

How about this as a test.

IIf([Dispatch] = -1 ,"Yes","No")
or
IIf([Dispatch] = True,"Yes","No") Note: True is the boolean, "True" would be the text string to DISPLAY it for the human eye.

That will force the evaluation to consider the underlying datatype value, and might show you that something else is in that field other than a boolean.

Thanks not working as the field that has True/False is a short text so guess this is stopping it working..

 

[CJ_London]

what is the datatype of your dispatch field? your initial post suggests it is boolean.

Is it even a field? or an unbound control?

 

[GPGeorge]

Thanks not working as the field that has True/False is a short text so guess this is stopping it working..

In that case, you could simply display the text value itself, i.e. "yes" or "no".

 

[Pat Hartman]

To get a boolean to display as Yes or No, use the format setting. Do NOT calculate using Format() or an IIf() since those change the data type and would make the field not updateable.

You might want to change the design in the table to display the boolean as Yes/No if that is what you want. That will help with queries and future forms.

 

[The_Doc_Man]

The question has again been brought up: "Why is TRUE = -1 for Access?"

It might just be a matter of confusion, really, since for Access the value of TRUE is not -1. It is really just anything that isn't all zeros. You can test it if you wish. Here is a copy/paste of an Immediate Window test that ANY non-zero value is TRUE

Debug.Print CBool( 4 )
True
Debug.Print CBool( 0 )
False
Debug.Print CBool( -1 )
True
Debug.Print CBool( &h0FF)
True

It is a side-effect of object-oriented inheritance that the Y/N field is ALSO an integer field. I would love to turn the above demonstration around but the problem is that Debug.Print doesn't recognize the value of TRUE. The name translation of TRUE is a one-way street.

There is a simple way to understand where -1 comes from. In Access, there is no "true" Boolean data type. Y/N (Boolean) fields are a TYPECAST of BYTE Integer. (Just like DATE is a TYPECAST of DOUBLE.) If we accept that FALSE is a value of all bits 0, then TRUE = NOT FALSE - a perfectly good little tautology. But remember that NOT is a bitwise operation, not a math operation. So the bitwise NOT operation flips all zero bits to one. In two's complement math, that "all bits one" is -1 when interpreting it as an integer. Because of that TYPECAST, anything that isn't coerced into another format will see the underlying datatype, a BYTE Integer, and interpret the contents numerically. It's a side effect of typecasting.

Therefore, if you want to know why some OTHER database system DOESN'T treat TRUE as -1, look to the way the DB implements the data type.

 

[Pat Hartman]

since for Access the value of TRUE is not -1

Almost Null always tests as False

This leads us to the problem that allowing null in a Y/N field causes. People search for True and they get what they expect but when they search for False, they don't get what they expect since Null is not true, nor is it false. It is simply Null. Using Not complicates the issue. Be cause if your test is Not True, you will get the False values but not the Null values because any value compared to Null is Null and that translates to False.

So if you want Null to count as Not True you need to be specific:

Where SomeField <> True Or IsNull(SomeField)

So if you want Null to count as "false" you need to include it:

Where SomeField = False or IsNull(SomeField)

 

[The_Doc_Man]

To finish off Pat's statement, if you allow a variable to be NULL and then do NOT <<variable>>, then because NULL propagates through expressions that have it, you STILL have NULL. The NULL propagated through the expression because VBA will not assign a "default" value to NULL. Which actually leads to this case: Where <<variable>> is NULL,

X = <<variable>>
Y = NOT <<variable>>
Z = ( X = Y )

ALL THREE of those... X, Y, and Z ... are NULL. (Don't forget that ( X + Y ) is a relational expression, so NULL propagates through that, too!)