Iif function--- What am I doing wrong?

[MaineLady]

I am trying to evaluate a field in an access database. I have entered iif([fieldx]="Contributor",1,0). The field that I am evaluating has the word Member or Contributor in it, but my formula always returns a zero (0). What am I doing wrong?

[alansidman]

Some thoughts on things to check.
1. Case sensitivity?
2. Spacing before the word or after the word "Contributor?" Either in your IIF statement or in your records.
3. Spelled correctly?

What happens when you change up your query and set the criteria in Fieldx to =Contributor? Do you get only the records which contain the term Contributor?

[June7]

What data type is Fieldx (is that the actual name)? Does it have a Lookup set in table? Why do you need to convert to 0 or 1?

[MaineLady]

The actual field name is Status, and yes it has a lookup table... person entering data can choose either Member or Contributor. I will be using the value set to determine what happens in another field.

[June7]

Still don't know why you need to convert to 0 and 1.

I NEVER set lookup in table. Show the RowSource sql of the lookup. Does it include an ID field? If so, I suspect the actual value of the field is the ID, not the descriptive text and that's why the expression fails.

[MaineLady]

OK... So I inserted another field and did not use the lookup table and now the function works. Now I want to delete the old field, but it tells me that I have to delete the relationship between the old field and the lookup table. When I do that, and I try to reopen my table, I get told that the object can't be found.

[June7]

Did you change the Lookup setting from combobox to textbox?

[MaineLady]

I am brand new at some of this sophisticated ACCESS stuff... I didn't change anything in the lookup setting... I created a new field that did not use a lookup table to fill it. When I used the iif function on that field it worked fine. Now I want to delete the field that did have the lookup and I am having a problem doing that.

I guess what I really need to do is sign up for an Access class somewhere.

[June7]

Change the lookup property on the old field from combobox to textbox. Try saving the revised table. Then try deleting the old field.

Do you have relationship set in Relationships window using the old field?

[MaineLady]

I changed lookup property to textbox... If I try to delete the field, I'm told that I first have to delete the relationship to the table that has the lookup choices in it. I did that twice already... but when I tried to reopen my main database, I got told that the object couldn't be found... both times... Thank God for Carbonite back ups... I've made several other modifications to the database this afternoon, and I really don't want to lose it again.

BTW... Thank you for your help. Sorry I'm such a dummy.

[June7]

I don't know why that field is being such a bugger. Could have been corrupted. If there is no relationship in the Relationship window and the lookup is removed (really, that should not even be necessary), and the field is not used anywhere else, then the field should be able to delete.

[MaineLady]

Right now the field has no entries in it. If I put the cursor in that field, it no longer shows the dropdown arrow. If I look at relationships, there is still a relationship between the table that had the lookup values in it and that field. If I click on that table and select DELETE, it tells me that the relationship has to be deleted first and asks if I want to delete the relationship and continue. I'm afraid to say YES because when I did that twice before, I ran into the "can't find the object" issue...

[June7]

Don't click on the table in Relationships, click on the line that connects the tables to delete the relationship between the fields.

[MaineLady]

To suppress my fear of losing the database again, I saved a copy to my desktop. I then deleted the table that had the lookup values in it... That process deleted the relationship between the field and the table. I was then able to delete the old field.

Thank you so much for your help.... and for being so patient with my level of experience.

[June7]

Just glad you got a solution.