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.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
Thanks not working as the field that has True/False is a short text so guess this is stopping it working..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")
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.
Debug.Print CBool( 4 ) True Debug.Print CBool( 0 ) False Debug.Print CBool( -1 ) True Debug.Print CBool( &h0FF) True
Almost Null always tests as Falsesince for Access the value of TRUE is not -1
X = <<variable>> Y = NOT <<variable>> Z = ( X = Y )