Trouble with IFF MS Access 2007

120 pts.
IIF function
Microsoft Access
Microsoft Access 2007
Ok what I want to do is very simple but nothing I have found is helping me.

I have a field that sometime contains a values but most of the time it is empty.

What I want to do is write an expression that says if there is something in this field, then “25”

I Believe that I should be using the “Is Not Null” but no matter how I use it I cant seem to get it to work.

The fields name is   “1st Position Held Elected or Appointed”

Any help would be greatly appreciative.

This is what I’ve tried and various versions of this:

Software/Hardware used:
MicroSoft Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

If I understand correctly,

<pre>=IIf([1st Position Held Elected or Appointed]=25,’25’,’not 25′)</pre>

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Could you please provide more details ? Do you want to do this in a query ? Also, the code of the different options you have tried didn't get pasted, please add it here. Thanks,
    86,030 pointsBadges:
  • Phil12550
    still having trouble with this. not sure if anyone understands what i need. You see what I want to do is if the field is empty then nothing. But if the field has something typed in it then I wan the value to = 25 You see the fields may have something typed in it like "Chief" or "Captain" or "president". There are many positions that someone can hold. If a person holds any position they get 25 points. So if the field is blank, then they get nothing. if i type in the criteria block [code] Is Not Null [code] then only those people who have a position assigned to them are listed and the possition is listed. these are the people who get 25 points. what i need to do is to replace the possition with "25" in simple terms if any value, then 25 everything that anyone has sent me does not work for one reason or another.
    120 pointsBadges:
  • carlosdl
    Phil12550, you created many similar questions. I suggested the following code (almost) in one of them:
    IIf(IsNull([1st Position Held Elected or Appointed]),0,25)
    Could you please let us know if that solves your problem ? If it doesn't work, please specify how the results you are getting are different from the results you expect (for each one of the suggestions you have tried).
    86,030 pointsBadges:
  • Randym
    If the field is bound to the database, and you are trying to put =iif(....) in the control source of the bound field, you can't do that. If you have only one record per form, you could have an unbound field with the =iif checking the database field and displaying what you want. If the form is showing multiple records, you must make the iif part of a query so you can bind that new column to the form.
    1,740 pointsBadges:
  • Darryn
    It is important to realise what is NULL, and what is a blank field, and how Access handles this when processing updates via forms, SQL etc. From my experience, I always find it best to test both ISNULL and "" i.e. blank, so your statement would be something like IIf(OR(IsNull([1st Position Held Elected or Appointed]),[1st Position Held Elected or Appointed]=""),0,25)
    765 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: