435,638 Members | 2,204 Online
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

# The result of iif equation in query appairs 0 and -1

 P: 6 After using iif equation in query (access 2013)to add new field, the result is -1 & 0 the equation is result: Expand|Select|Wrap|Line Numbers iif([fieldname]=yes, yes, no] I want the result to appear yes or no (or check box) Jan 4 '14 #1

In a yes/no field, and in VBA, the True/Yes = -1 and False/No = 0
What you have
Expand|Select|Wrap|Line Numbers
1. iif([fieldname]=yes, yes, no]
is returning the values
I would suggest is placing quotes:
Expand|Select|Wrap|Line Numbers
1. iif([fieldname]=yes, "yes", "no"]
which will return the string

Now remember, this will be a string value for the calculated field.

5 Replies

 Expert Mod 5K+ P: 5,397 In a yes/no field, and in VBA, the True/Yes = -1 and False/No = 0 What you have Expand|Select|Wrap|Line Numbers iif([fieldname]=yes, yes, no] is returning the values I would suggest is placing quotes: Expand|Select|Wrap|Line Numbers iif([fieldname]=yes, "yes", "no"] which will return the string Now remember, this will be a string value for the calculated field. Jan 4 '14 #2

 P: 6 Thank you very much. the problem is solved. but another problem appeared. the following equation gives errors Expand|Select|Wrap|Line Numbers iif([fieldname]=[result],"yes", "no") [fieldname] is yes/no field from table source. [result] is a field created in query as the following result: Expand|Select|Wrap|Line Numbers iif([fieldename2]=yes, "yes", "no") Jan 4 '14 #3

 Expert Mod 5K+ P: 5,397 Ahmedamer New Question - New Thread For clarity; we ask that a single topic be handled per thread. You may link back to this thread if required for contex. Please, when you repost clarify the following: the following equation gives errors You do not specify the error(s) given nor the context within which the equation is used (form, query, table, vba, etc...) Please use the [CODE/] button to place [code] [/code] around script or formated tables. This is a site requirement. Jan 4 '14 #4

 Expert Mod 15k+ P: 31,495 I think you need to understand that Yes, No, True & False are absolutely not the same as "Yes", "No", "True" & "False". This lack of understanding is the cause of your second question. Really, they are both centred around a single misunderstanding. If you want a Yes/No value, then you probably need a numeric field. Normally, this will show as -1 and 0 if there is nothing to help it realise you want it displayed as Yes/No. Fields in tables can be specified as Yes/No, True/False or boolean. All add up to fundamentally the same thing in reality. If you are showing the result of a numeric calculation (or any numeric formula) in a query then you need to look at the Properties window and set the Format property to something like Yes/No. I'm not aware that you can specify a CheckBox type control for a calculated field in a query. These show when boolean fields are used though, even in a query. You can also, if you want to convert your usable numeric values to less usable display values, use format : Expand|Select|Wrap|Line Numbers Format([fieldname],"Yes/No") My advice would be to avoid any such conversion where possible though, as it tends to lead to further problems. One of which you've stumbled over already. Jan 5 '14 #5

 Expert Mod 5K+ P: 5,397 The "yes/no" confusion (IMHO) is due to MS using that as the type-cast descriptor in the table designer. I can understand their desire; however, it might have been better to have used "true/false" This become very clear in the format property of the "Yes/No" type cast field where they have: [True/False - True] [Yes/No - Yes] [On/Off - Off] MS is hiding the fact that they are using the boolean -1 = true and 0 = false As for the conversion thing... NeoPa has a very valid point. Personally, I only convert these to a text string when I need a text value for a report and never anything else (NeoPa: Create a Yes/No field in a table, in the general tab set to "true/false", in the lookup tab, select the text box... now the field will read "true/false" or "Yes/No" - this will carry thru to the query. The combobox option is spooky to work with for these as you can really get creative and obscure (evil-laugh)) Jan 5 '14 #6