<div>
Can you post a sample copy of your database, or the exact IIF statement you have written in its entirety?<br />
<br />

</div>


<div>
Are both tables and fields in the data source of the report?
</div>


<div>
IIf ([Tbl_name]![field name]=number value,then I want it to pull a field from another table here if the number value is true, else pull from another table’s field.<br />
<br />
so if you are dealing with &nbsp;three tables<br />
<br />
&nbsp; &nbsp; &nbsp; &nbsp;tbl1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tbl2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tbl3<br />
IIf ([Tbl_name]![field name]= num,([2nd Tbl_name]![field name],([3rdrTbl_name]![fie<wbr />ld name])<br />
<br />
provided all datasources are available &nbsp;this i believe would &nbsp;fullfill your requirement if,<br />
&nbsp;I have understood you correctly.<br />
<br />
Explanation of IIF<br />
iif( value /field = &nbsp;chk value , truepart ,falsepart) &nbsp;the true part &nbsp;-the value after the first comma is s the value you allocated if the outcome of the check is true <br />
&nbsp;obviously if the &nbsp;outcome does not match the required value then the value &nbsp;allocated is the value you placed after the second comma &nbsp;- the false part.<br />
<br />
hope Ihave understood you &nbsp;<br />
<br />
regards<br />
jo
</div>


<div>
IEHP1, <br />
<br />
If you use the IIF in a query, you may try to use this:<br />
IIf ([Tbl_name].[field name]=number value . . .<br />
<br />
Sincerely,<br />
Ed
</div>


<div>
So I apologize for being vague, I would like to take the value of one table if true, it needs to pull that record and then put a &quot;, [field name from another table].&quot; as added to the first true condition (just appending it to the true condition).<br />
<br />
Example: <br />
IIf([TblToSendAllDisputeLe<wbr />ttersToEDI<wbr />]![D01_Lis<wbr />t_Detail]=<wbr />1,[tbl_Cor<wbr />r_Main]![R<wbr />esponsible<wbr /> IPA/Hosp],[D01_Crosswalk]!<wbr />[Letter Description])<br />
<br />
So I am trying to say if the D01_List_Detail =1, then pull the value for 1 and append the Responsible IPA/Hosp at the end of the sentence (like sentence sentence sentence sentence, [Responsible IPA/Hosp] and underlined for the Responsible IPA/Hosp]. <br />
<br />
Thank you in advance
</div>


<div>
So are these tables used in the source of the table?<br />
<br />
If they any of them aren't you'll need to use something like DLookUp.
</div>


<div>
IEHP1,<br />
<br />
I did not see any problem with your IIF function. It surely worked well if those fields are well referenced. <br />
<br />
If you can post a screen shot of your report in design view, we can be able to look at how you place your IIF in the report.<br />
<br />
You might missed the &quot;=&quot; or might be the &quot;&amp;&quot;&nbsp;for you use the IIF concatenating other sentence.<br />
<br />
Ed
</div>


<div>
Ed<br />
<br />
The Iif will only work if all 3 tables and fields are available in the record source of the report.<br />
<br />
We still don't know that.:)
</div>


<div>
Letter Description: IIf([Tbl_Corr_Details]![D0<wbr />1_List_Det<wbr />ail]=13,[T<wbr />bl_Corr_De<wbr />tails]![D0<wbr />1_OtherCom<wbr />ments],[D0<wbr />1_Crosswal<wbr />k]![Letter<wbr /> Description]) Or IIf([Tbl_Corr_Details]![D0<wbr />1_List_Det<wbr />ail]=1,[Tb<wbr />l_Corr_Det<wbr />ails]![D01<wbr />_HPRespons<wbr />ibility] &amp;&nbsp;&quot;, &quot;&nbsp;&amp; [tbl_Corr_Disputes_Main]![<wbr />Responsibl<wbr />e IPA/Hosp],[D01_Crosswalk]!<wbr />[Letter Description])<br />
<br />
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2011/10_w44/516494/Letter-Description-field-and-the.doc" target="_blank" class="file-inline" title="Notice the Letter Description field is suppose to be pulling in the above IIf statement, but it isn't adding the [tbl_Corr_Disputes_Main]![Responsible IPA/Hosp] after the & sign??  (54 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Letter-Description-field-and-the.doc</a>
</div>


Notice the Letter Description field is suppose to be pulling in the above IIf statement, but it isn't adding the [tbl_Corr_Disputes_Main]![Responsible IPA/Hosp] after the & sign?? 

Letter-Description-field-and-the.doc

<div>
You have 2 separate IIf statements and you've joined them with Or.<br />
<br />
If you replace the Or with &amp;&nbsp;you won't get the error but I don't think you'll get the correct result.<br />
<br />
What should the result be?
</div>


<div>
The result should be to pull in the sentence for numeric value 1 (if that is true) into the report and add a &quot;&nbsp;, then the name of the Responsible IPA/Hosp&quot; , else pull in the Letter Description of the other table ([D01_Crosswalk]). <br />
<br />
example: &nbsp;The claim is the financial responsibility of the member's IPA, [Responsible IPA/Hosp].<br />
<br />
So you see, whatever the name of the IPA/Hosp should be filled in next to the comma and the sentence shown. If it is not 1, then pull in the other numeric value from the D01_Crosswalk table. <br />
<br />
Do you understand?
</div>


<div>
Where's the 1 in the example?
</div>


<div>
the 1 is the numeric value of the field D01_List_Detail in the table I gave you. So if that field equals 1, then pull the D01_HPResponsiblity from the same table. If not pull the Letter Description field of the other table. <br />
<br />
The [Tbl_Corr_Details]![D01_Li<wbr />st_Detail]<wbr /> is linked to [D01_Crosswalk]![D01_Check<wbr />BoxID] (just a field name, its really not a checkbox or anything like that) and Letter Description is a field in D01_Crosswalk table.<br />
<br />
Would you like a screenshot?
</div>


<div>
Sorry but you haven't attached a table, the only attachment has been a Word document with a screenshot of a report/form in design view.<br />
<br />
Did you try replacing the Or with a &amp;?
</div>


<div>
Do you get a better idea if you see this?<br />
<br />
I did try replacing Or with a &amp;, it didn't work, then I went back to check if it kept the change, but it changes it back to Or automatically.......<br />
<a href="https://filedb.experts-exchange.com/incoming/2011/10_w44/516522/Letter-Description-field-and-the.doc" target="_blank" class="file-inline" title="The links are here  (112 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Letter-Description-field-and-the.doc</a>
</div>


<div>
I know the first part works up until the &quot;Or&quot; because I cut it out of there and tested the first part and it works, but it proves that something after that first part is wrong? 
</div>


<div>
Try this syntax:<br />
<br />
Letter Description: IIf([Tbl_Corr_Details]![D0<wbr />1_List_Det<wbr />ail]=13,II<wbr />f([Tbl_Cor<wbr />r_Details]<wbr />![D01_List<wbr />_Detail]=1<wbr />,[Tbl_Corr<wbr />_Details]!<wbr />[D01_HPRes<wbr />ponsibilit<wbr />y] &amp;&nbsp;&quot;, &quot;&nbsp;&amp; [tbl_Corr_Disputes_Main]![<wbr />Responsibl<wbr />e IPA/Hosp],[Tbl_Corr_Detail<wbr />s]![D01_Ot<wbr />herComment<wbr />s]),[D01_C<wbr />rosswalk]!<wbr />[Letter Description])<br />
<br />
Sincerely,<br />
Ed
</div>


<div>
nope sorry your syntax pulled in the numeric 1 value of D01_List_Detail (The claims is the financial responsibility of ~~~~~~~~, etc. etc.), but it didn't pull in the &quot;, [Responsible IPA/Hosp]&quot;. <br />
<br />
I very much appreciate your comment though and if you can help me with this I would so appreciate it and be your best friend. I will keep trying little tweaks on my end. <br />
<br />
What do you think though, it seems logical right? I mean, why isn't it working?
</div>


<div>
You might need to check your the relationships of your table. Or, there might be no value in [Responsible IPA/Hosp].<br />
<br />
Try to add [Responsible IPA/Hosp] in your query and see if you any values when you run the query.<br />
<br />
Sincerely,<br />
Ed
</div>


<div>
it also pulls the numeric value 13's letter description when I tested it, but for some reason, it seems to not like the &amp;&nbsp;&quot;, &quot;&nbsp;&amp; [tbl_Corr_Disputes_Main]![<wbr />Responsibl<wbr />e IPA/Hosp], syntax regarding numeric value 1's letter description i think (because it won't pull the information in)? <br />
<br />
I checked the Tbl_Corr_Disputes_Main to see if there is actually a Responsible IPA/Hosp in there too and there is so I know it just isn't pulling it in??
</div>


<div>
Why not test the 2nd part?<br />
<br />
By the way, I really can't see how the &amp;&nbsp;would be replaced by Or.<br />
<br />
It doesn't make logical sense, to me anyway &amp;&nbsp;means And not Or.<br />
<br />
The relationship diagram kind of helps but it would be better if you posted some sample data and expected results, or even a cut down version of the database with only the tables required - without any sensitive data of course.
</div>


<div>
how am i suppose to provide you with that? I have never done what you are requesting before? <br />
<br />
I mean, do people make a separate database and then send it to you as a file? how does that work? Please explain.....
</div>


<div>
How about this:<br />
<br />
Letter Description: IIf([Tbl_Corr_Details]![D0<wbr />1_List_Det<wbr />ail]=13,[T<wbr />bl_Corr_De<wbr />tails]![D0<wbr />1_OtherCom<wbr />ments], IIf([Tbl_Corr_Details]![D0<wbr />1_List_Det<wbr />ail]=1,[Tb<wbr />l_Corr_Det<wbr />ails]![D01<wbr />_HPRespons<wbr />ibility] &amp;&nbsp;&quot;, &quot;&nbsp;&amp; [tbl_Corr_Disputes_Main]![<wbr />Responsibl<wbr />e IPA/Hosp],[D01_Crosswalk]!<wbr />[Letter Description]))<br />
<br />
Sincerely,<br />
Ed
</div>


<div>
I don't think you need to post or attach data.<br />
<br />
The expression Ed posted should work.<br />
<br />
It doesn't return the value in [D01_List_Detail] anywhere.
</div>


<div>
oh my goodness!! your logic pulled it in...... BUT instead of pulling in the sentence written out in plain english from the D01_Crosswalk table it is linked on, it pulled the number 0.<br />
<br />
The example I tested had the result: &nbsp; &nbsp; &nbsp; 0, then the name of the Responsible IPA/Hosp.<br />
<br />
It is just pointed to the wrong field right? Or what is going on with that?<br />
<br />
Thank you so much so far though!!!
</div>


<div>
sorry I forgot to explain that the number 0 is the stored value for my test for that D01_HPResponsibility field.
</div>


<div>
The 0 is the value of [Tbl_Corr_Details]![D01_HP<wbr />Responsibi<wbr />lity] not any value from D01_Crosswalk table.<br />
<br />
The query syntax worked properly and would display whatever values you have on those fields.<br />
<br />
Ed
</div>


<div>
i will play with it, but what I am trying to do is pull the Letter Description field from D01_Crosswalk for the first part because it is linked. I don't know if that sounds dumb to you, that's why I will play with it and let you know if I can get it working.<br />
<br />
Thankyou so much and you have an awesome day!!!
</div>


<div>
Ed stepped in and provided the expression the first time, it didn't work. Then he provided the second expression and it worked, but i had it pointed to the wrong field and I corrected it. Thanks Ed.
</div>


<div>
Glad you got it solved.<br />
<br />
You can simplify this sort of thing by not using the table and field name.<br />
<br />
The field name on it's own should work as long as no other tables have a field with that name.<br />
<br />
If other tables do have a field with the same name you'll need the table name.<br />
<br />
Letter Description: IIf([D01_List_Detail]=13,[<wbr />D01_OtherC<wbr />omments], IIf([D01_List_Detail]=1,[D<wbr />01_HPRespo<wbr />nsibility]<wbr /> &amp;&nbsp;&quot;, &quot;&nbsp;&amp; [Responsible IPA/Hosp],[Letter Description]))<br />

</div>


<div>
<div class="content wysiwyg-content">
The following is my syntax and it isn't pulling it into a report that is created.<br />
<br />
Tag: IIf ([Tbl_name]![field name]=number value,then I want it to pull a field from another table here if the number value is true, else pull from another table’s field.<br />
<br />
Please help with this one,<br />
Thankyou
</div>
</div>


The following is my syntax and it isn't pulling it into a report that is created.

Tag: IIf ([Tbl_name]![field name]=number value,then I want it to pull a field from another table here if the number value is true, else pull from another table’s field.

Please help with this one,
Thankyou

IIf statement

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.