Trouble with IFF MS 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
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
ASKED:
February 14, 2010 1:30 PM
UPDATED:
February 16, 2010 3:02 PM
Answer Wiki:
If I understand correctly,
<pre>=IIf([1st Position Held Elected or Appointed]=25,'25','not 25')</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
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,
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
Is Not Null
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.
Phil12550, you created many similar questions.
I suggested the following code (almost) in one of them:
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).
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.
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)