By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 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
  1. iif([fieldname]=yes, yes, no]
I want the result to appear yes or no (or check box)
Jan 4 '14 #1

✓ answered by zmbd

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.

Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,287
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.
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
  1. 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
  1. iif([fieldename2]=yes, "yes", "no")
Jan 4 '14 #3

zmbd
Expert Mod 5K+
P: 5,287
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

NeoPa
Expert Mod 15k+
P: 31,186
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
  1. 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

zmbd
Expert Mod 5K+
P: 5,287
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

Post your reply

Sign in to post your reply or Sign up for a free account.