[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

IIf statement

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
0
IEHP1
Asked:
IEHP1
  • 14
  • 9
  • 7
  • +2
1 Solution
 
mbizupCommented:
Can you post a sample copy of your database, or the exact IIF statement you have written in its entirety?

0
 
NorieCommented:
Are both tables and fields in the data source of the report?
0
 
jo_mCommented:
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.

so if you are dealing with  three tables

       tbl1                                           tbl2                                             tbl3
IIf ([Tbl_name]![field name]= num,([2nd Tbl_name]![field name],([3rdrTbl_name]![field name])

provided all datasources are available  this i believe would  fullfill your requirement if,
 I have understood you correctly.

Explanation of IIF
iif( value /field =  chk value , truepart ,falsepart)  the true part  -the value after the first comma is s the value you allocated if the outcome of the check is true
 obviously if the  outcome does not match the required value then the value  allocated is the value you placed after the second comma  - the false part.

hope Ihave understood you  

regards
jo
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
MINDSUPERBCommented:
IEHP1,

If you use the IIF in a query, you may try to use this:
IIf ([Tbl_name].[field name]=number value . . .

Sincerely,
Ed
0
 
IEHP1Author Commented:
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 ", [field name from another table]." as added to the first true condition (just appending it to the true condition).

Example:
IIf([TblToSendAllDisputeLettersToEDI]![D01_List_Detail]=1,[tbl_Corr_Main]![Responsible IPA/Hosp],[D01_Crosswalk]![Letter Description])

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].

Thank you in advance
0
 
NorieCommented:
So are these tables used in the source of the table?

If they any of them aren't you'll need to use something like DLookUp.
0
 
MINDSUPERBCommented:
IEHP1,

I did not see any problem with your IIF function. It surely worked well if those fields are well referenced.

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.

You might missed the "=" or might be the "&" for you use the IIF concatenating other sentence.

Ed
0
 
NorieCommented:
Ed

The Iif will only work if all 3 tables and fields are available in the record source of the report.

We still don't know that.:)
0
 
IEHP1Author Commented:
Letter Description: IIf([Tbl_Corr_Details]![D01_List_Detail]=13,[Tbl_Corr_Details]![D01_OtherComments],[D01_Crosswalk]![Letter Description]) Or IIf([Tbl_Corr_Details]![D01_List_Detail]=1,[Tbl_Corr_Details]![D01_HPResponsibility] & ", " & [tbl_Corr_Disputes_Main]![Responsible IPA/Hosp],[D01_Crosswalk]![Letter Description])

 Letter-Description-field-and-the.doc
0
 
NorieCommented:
You have 2 separate IIf statements and you've joined them with Or.

If you replace the Or with & you won't get the error but I don't think you'll get the correct result.

What should the result be?
0
 
IEHP1Author Commented:
The result should be to pull in the sentence for numeric value 1 (if that is true) into the report and add a " , then the name of the Responsible IPA/Hosp" , else pull in the Letter Description of the other table ([D01_Crosswalk]).

example:  The claim is the financial responsibility of the member's IPA, [Responsible IPA/Hosp].

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.

Do you understand?
0
 
NorieCommented:
Where's the 1 in the example?
0
 
IEHP1Author Commented:
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.

The [Tbl_Corr_Details]![D01_List_Detail] is linked to [D01_Crosswalk]![D01_CheckBoxID] (just a field name, its really not a checkbox or anything like that) and Letter Description is a field in D01_Crosswalk table.

Would you like a screenshot?
0
 
NorieCommented:
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.

Did you try replacing the Or with a &?
0
 
IEHP1Author Commented:
Do you get a better idea if you see this?

I did try replacing Or with a &, it didn't work, then I went back to check if it kept the change, but it changes it back to Or automatically.......
Letter-Description-field-and-the.doc
0
 
IEHP1Author Commented:
I know the first part works up until the "Or" 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?
0
 
MINDSUPERBCommented:
Try this syntax:

Letter Description: IIf([Tbl_Corr_Details]![D01_List_Detail]=13,IIf([Tbl_Corr_Details]![D01_List_Detail]=1,[Tbl_Corr_Details]![D01_HPResponsibility] & ", " & [tbl_Corr_Disputes_Main]![Responsible IPA/Hosp],[Tbl_Corr_Details]![D01_OtherComments]),[D01_Crosswalk]![Letter Description])

Sincerely,
Ed
0
 
IEHP1Author Commented:
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 ", [Responsible IPA/Hosp]".

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.

What do you think though, it seems logical right? I mean, why isn't it working?
0
 
MINDSUPERBCommented:
You might need to check your the relationships of your table. Or, there might be no value in [Responsible IPA/Hosp].

Try to add [Responsible IPA/Hosp] in your query and see if you any values when you run the query.

Sincerely,
Ed
0
 
IEHP1Author Commented:
it also pulls the numeric value 13's letter description when I tested it, but for some reason, it seems to not like the & ", " & [tbl_Corr_Disputes_Main]![Responsible IPA/Hosp], syntax regarding numeric value 1's letter description i think (because it won't pull the information in)?

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??
0
 
NorieCommented:
Why not test the 2nd part?

By the way, I really can't see how the & would be replaced by Or.

It doesn't make logical sense, to me anyway & means And not Or.

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.
0
 
IEHP1Author Commented:
how am i suppose to provide you with that? I have never done what you are requesting before?

I mean, do people make a separate database and then send it to you as a file? how does that work? Please explain.....
0
 
MINDSUPERBCommented:
How about this:

Letter Description: IIf([Tbl_Corr_Details]![D01_List_Detail]=13,[Tbl_Corr_Details]![D01_OtherComments], IIf([Tbl_Corr_Details]![D01_List_Detail]=1,[Tbl_Corr_Details]![D01_HPResponsibility] & ", " & [tbl_Corr_Disputes_Main]![Responsible IPA/Hosp],[D01_Crosswalk]![Letter Description]))

Sincerely,
Ed
0
 
NorieCommented:
I don't think you need to post or attach data.

The expression Ed posted should work.

It doesn't return the value in [D01_List_Detail] anywhere.
0
 
IEHP1Author Commented:
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.

The example I tested had the result:       0, then the name of the Responsible IPA/Hosp.

It is just pointed to the wrong field right? Or what is going on with that?

Thank you so much so far though!!!
0
 
IEHP1Author Commented:
sorry I forgot to explain that the number 0 is the stored value for my test for that D01_HPResponsibility field.
0
 
MINDSUPERBCommented:
The 0 is the value of [Tbl_Corr_Details]![D01_HPResponsibility] not any value from D01_Crosswalk table.

The query syntax worked properly and would display whatever values you have on those fields.

Ed
0
 
IEHP1Author Commented:
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.

Thankyou so much and you have an awesome day!!!
0
 
MINDSUPERBCommented:
You are welcome and have a nice day too.

Ed
0
 
IEHP1Author Commented:
i got it!!!! Thanks Ed.
0
 
IEHP1Author Commented:
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.
0
 
NorieCommented:
Glad you got it solved.

You can simplify this sort of thing by not using the table and field name.

The field name on it's own should work as long as no other tables have a field with that name.

If other tables do have a field with the same name you'll need the table name.

Letter Description: IIf([D01_List_Detail]=13,[D01_OtherComments], IIf([D01_List_Detail]=1,[D01_HPResponsibility] & ", " & [Responsible IPA/Hosp],[Letter Description]))
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 14
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now