Conv. 0 and 1 to Yes, No

[Guest]

I am generating a query on a database and one of the fields has either a 1 or
zero which represent a Yes, or No to the application that uses the database.
In my query I want to display a Yes, or No instead of the 1 or 0.

I was trying to use an IIf expression but I get a message: Data Type
Mismatch.
Here is my expression IIf([NvrExpire]="No","Never Expire","Use Expiration
Date")
I have tried all variations of this using the 0 or 1 after the (=) and
changing the text to display.

Am I getting a data type mismatch because the database field design requires
a 0 or 1?

Is there a way that I can have the results posted in a new field?

 

[Jeff Boyce]

Didn't you say the field has either a 1 or a 0? Why would you be testing
for "No"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I want to format the results in the Query as Yes or No instead of 1 or 0.

Didn't you say the field has either a 1 or a 0? Why would you be testing
for "No"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am generating a query on a database and one of the fields has either a 1
or
zero which represent a Yes, or No to the application that uses the
database.
In my query I want to display a Yes, or No instead of the 1 or 0.

I was trying to use an IIf expression but I get a message: Data Type
Mismatch.
Here is my expression IIf([NvrExpire]="No","Never Expire","Use Expiration
Date")
I have tried all variations of this using the 0 or 1 after the (=) and
changing the text to display.

Am I getting a data type mismatch because the database field design
requires
a 0 or 1?

Is there a way that I can have the results posted in a new field?

 

[Michel Walsh]

Hi,




then,


SELECT iif(fieldName, "yes", "no") FROM ...


or


SELECT Format( fieldName, "yes/no") FROM ...



The last one will be a result accordingly to the regional setting (not
necessary in English).



Hoping it may help,
Vanderghast, Access MVP

I want to format the results in the Query as Yes or No instead of 1 or 0.

Didn't you say the field has either a 1 or a 0? Why would you be testing
for "No"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am generating a query on a database and one of the fields has either a
1
or
zero which represent a Yes, or No to the application that uses the
database.
In my query I want to display a Yes, or No instead of the 1 or 0.

I was trying to use an IIf expression but I get a message: Data Type
Mismatch.
Here is my expression IIf([NvrExpire]="No","Never Expire","Use
Expiration
Date")
I have tried all variations of this using the 0 or 1 after the (=) and
changing the text to display.

Am I getting a data type mismatch because the database field design
requires
a 0 or 1?

Is there a way that I can have the results posted in a new field?

 

[Jeff L]

In your expression, you are testing for a string value, "No", not a 0
for No. I believe that's what the other user was trying to hint at for
you. Because you are testing for a string, you are getting the data
type mismatch error.

 

[Guest]

I did not understand what the datatype of the field [NvrExpire] was. Is it
text or number?
If it is number then use --
IIf([NvrExpire]=0,"Never Expire","Use Expiration Date")

If it is text then use --
IIf([NvrExpire]="0","Never Expire","Use Expiration Date")

 

[JethroUK©]

add a new field to the table
define it as yes/no (checkbox)
include both fields in an update query

set mynewfield = NvrExpire

 

[Guest]

Thank You everyone for you suggestions but I still can not get the query to
work the way I am expecting.

The field is defined as a number. The possible values are 0 = Yes, or 1 =
No. When the query is generated it reports either 0 or 1 in this field. I
would like to reformat the 0 to display "Yes" and if the value is 1 I want to
display "No".

If anyone else has a suggestion for doing this it will be greatly
appreciated. If not then I will work with the 0 or 1.

Thanks again for your suggestions.

I did not understand what the datatype of the field [NvrExpire] was. Is it
text or number?
If it is number then use --
IIf([NvrExpire]=0,"Never Expire","Use Expiration Date")

If it is text then use --
IIf([NvrExpire]="0","Never Expire","Use Expiration Date")

I am generating a query on a database and one of the fields has either a 1 or
zero which represent a Yes, or No to the application that uses the database.
In my query I want to display a Yes, or No instead of the 1 or 0.

I was trying to use an IIf expression but I get a message: Data Type
Mismatch.
Here is my expression IIf([NvrExpire]="No","Never Expire","Use Expiration
Date")
I have tried all variations of this using the 0 or 1 after the (=) and
changing the text to display.

Am I getting a data type mismatch because the database field design requires
a 0 or 1?

Is there a way that I can have the results posted in a new field?

 

[Guest]

In design view add a new column like this --
Expiration: IIF( [NvrExpire] = 0, "Yes", "no")

Thank You everyone for you suggestions but I still can not get the query to
work the way I am expecting.

The field is defined as a number. The possible values are 0 = Yes, or 1 =
No. When the query is generated it reports either 0 or 1 in this field. I
would like to reformat the 0 to display "Yes" and if the value is 1 I want to
display "No".

If anyone else has a suggestion for doing this it will be greatly
appreciated. If not then I will work with the 0 or 1.

Thanks again for your suggestions.

I did not understand what the datatype of the field [NvrExpire] was. Is it
text or number?
If it is number then use --
IIf([NvrExpire]=0,"Never Expire","Use Expiration Date")

If it is text then use --
IIf([NvrExpire]="0","Never Expire","Use Expiration Date")

I am generating a query on a database and one of the fields has either a 1 or
zero which represent a Yes, or No to the application that uses the database.
In my query I want to display a Yes, or No instead of the 1 or 0.

I was trying to use an IIf expression but I get a message: Data Type
Mismatch.
Here is my expression IIf([NvrExpire]="No","Never Expire","Use Expiration
Date")
I have tried all variations of this using the 0 or 1 after the (=) and
changing the text to display.

Am I getting a data type mismatch because the database field design requires
a 0 or 1?

Is there a way that I can have the results posted in a new field?

 

[Guest]

That is exactly what I was trying to do. Thanks a lot Karl!!

In design view add a new column like this --
Expiration: IIF( [NvrExpire] = 0, "Yes", "no")

Thank You everyone for you suggestions but I still can not get the query to
work the way I am expecting.

The field is defined as a number. The possible values are 0 = Yes, or 1 =
No. When the query is generated it reports either 0 or 1 in this field. I
would like to reformat the 0 to display "Yes" and if the value is 1 I want to
display "No".

If anyone else has a suggestion for doing this it will be greatly
appreciated. If not then I will work with the 0 or 1.

Thanks again for your suggestions.

I did not understand what the datatype of the field [NvrExpire] was. Is it
text or number?
If it is number then use --
IIf([NvrExpire]=0,"Never Expire","Use Expiration Date")

If it is text then use --
IIf([NvrExpire]="0","Never Expire","Use Expiration Date")


:

I am generating a query on a database and one of the fields has either a 1 or
zero which represent a Yes, or No to the application that uses the database.
In my query I want to display a Yes, or No instead of the 1 or 0.

I was trying to use an IIf expression but I get a message: Data Type
Mismatch.
Here is my expression IIf([NvrExpire]="No","Never Expire","Use Expiration
Date")
I have tried all variations of this using the 0 or 1 after the (=) and
changing the text to display.

Am I getting a data type mismatch because the database field design requires
a 0 or 1?

Is there a way that I can have the results posted in a new field?