none
Nested IIf in a Report - Access 2010

    Question

  • I have added the following expression as a control source on a report:

    =IIf([Report]![full_name]="","",IIf([old_number]="",[client_number] & "-" & [matter_number],[old_number]))

    It doesn't print anything on the report whether old_number is null or not.  What is wrong with my logic? old_number is text. client_number and matter_number are integers. They are all in the query that the report is based on.  

    Tuesday, June 11, 2013 9:02 PM

Answers

  • 1.  Just want to confirm that [old_number] is of Text data-type as per your first post?

    2.  Perhaps, we should test the expression independently from the Report.  In the Query/SQL String being used as the RecordSource for the Report, add a Calculated Column as follows:

    * In grid view:

    Result:IIf(
      Trim([full_name] & "") = "",
      "",
      IIf(
        Trim([old_number] & "") = "",
        [client_number] & "-" & [matter_number],
        [old_number]
         )
       )

    * In SQL view:

    SELECT {other Fields},
    IIf( Trim([full_name] & "") = "", "", IIf( Trim([old_number] & "") = "", [client_number] & "-" & [matter_number], [old_number] ) ) AS Result

    FROM...

    (line-breaks are for posting clarity only and you don't need line-breaks in your grid / SQL).

    Now open the Datasheet view of the Query/SQL and check the values of the Calculated Column [Result]. Do they show the values of [old_number] as per your algorithm or still blank?

    If the Datasheet View works correctly, then you can use the modified Query/SQL as the RecordSource for the Report and bind your TextBox to the Calculated Column (Field) [Result].

          


    Van Dinh


    • Edited by Van DinhMVP Tuesday, June 11, 2013 11:31 PM
    • Marked as answer by BWanner Wednesday, June 12, 2013 12:14 AM
    Tuesday, June 11, 2013 11:23 PM

All replies

  • It depends on what you want to get out of it.

    Do you want to get a list of Records that have no value for the Full Name and return either the Client + Matter Number or Return the Old Number based on the availability of the Old Number?

    That would be like:

    =IIf([Report]![full_name]="" And IIf([old_number]="",[client_number] & "-" & [matter_number],[old_number]))


    Chris Ward

    Tuesday, June 11, 2013 9:23 PM
  • However if [Old_Number] is a number field then you would want to change it to

    =IIf([Report]![full_name]="" And IIf([old_number] Is Null,[client_number] & "-" & [matter_number],[old_number]))


    Chris Ward

    Tuesday, June 11, 2013 9:25 PM
  • If full_name is null leave it blank

    If full_name is not null and old_number is not null print old_number

    If full_name is not null and old_number is null print client_number and matter_number concatenated

    Tuesday, June 11, 2013 9:41 PM
  • Try:

    =IIf(
      Trim([full_name] & "") = "",
      "",
      IIf(
        Trim([old_number] & "") = "",
        [client_number] & "-" & [matter_number],
        [old_number]
         )
       )

    The criteria above trap for both Null and blank characters, e.g. space characters.


    Van Dinh

    Tuesday, June 11, 2013 9:54 PM
  • I think you mean

    If Full Name is "" Don't add the record to the Report otherwise

    If old_number is not null Add Full Name and old_number Other wise

    Print Full Name client_number and matter_number concatenated


    Chris Ward

    Tuesday, June 11, 2013 9:57 PM
  • That would work also.  Right now I have the another field setup to print "No Conflicts Found" if full_name is null.

    Beth Wanner

    Tuesday, June 11, 2013 10:01 PM
  • Thanks, this is closer. Now it prints the concatenated client-matter number.  Now I just need to get it to print the old_number when it exists.  

    Beth Wanner

    Tuesday, June 11, 2013 10:14 PM
  • Are you referring to my suggestion?

    It should print the [old_number] if full_name has printable/visible characters and old_number has printable/visible characters.  This is equivalent to your 2nd point in the algorithm but expressed more accurately.


    Van Dinh

    Tuesday, June 11, 2013 10:23 PM
  • Van, 

    Yes, I was replying to you.  Yes, you have expressed it more accurately.  Thanks.


    Beth Wanner

    Tuesday, June 11, 2013 10:25 PM
  • ... and it does not print the [old_number] when the two conditions are satisfied?


    Van Dinh

    Tuesday, June 11, 2013 10:33 PM
  • Van,

    It does not print the [old_number].  It leaves it blank.  I just double-checked my query and the and the data is there.  The field is left blank only when there is an old number.  


    Beth Wanner

    Tuesday, June 11, 2013 10:45 PM
  • Could you please post relevant details of your Tables and the SQL String (or the SQL String of the saved Query) being used as the RecordSource for the Report.


    Van Dinh

    Tuesday, June 11, 2013 10:52 PM
  • Van, 

    I shortened some of the names when I posted on this forum and the report is based on a temporary table, but here is the SQL:

    Private Sub Command85_Click()

    If OptionClient.Value = -1 Then
        'if chosing an existing client open that selection box and abandon this form
        DoCmd.OpenForm "ClientSelectionBox", acNormal
        DoCmd.Close acForm, "conflictbox"
        
    Else
    If OptionNew.Value = 0 Then
        
    Else
    If OptionNew.Value = -1 Then

    Dim db As Database
    Dim MySQL As String
    Dim MySQL2 As String
    Dim MySQL3 As String
    Set db = CurrentDb

    'create a temporary table to add search names
    If Not IsNull(DLookup("Type", "MSYSObjects", "Name='Add_Or_Edit_Conflict_Search'")) Then
        MsgBox "An object already exists with this name"
    Else
        db.Execute "CREATE TABLE Add_Or_Edit_Conflict_Search (party varCHAR primary key);"
    End If

    'open temporary table and allow time to enter data
    DoCmd.OpenForm "Edit_Conflict_Search", acFormDS
    Do While (SysCmd(acSysCmdGetObjectState, acForm, "Edit_Conflict_Search") <> 0)
       DoEvents
    Loop

    'create temporary table of potential conflicts
    Dim RS As Recordset
    Set RS = CurrentDb.OpenRecordset("SELECT Party FROM Add_Or_Edit_Conflict_Search")
    RS.MoveFirst
     
    While Not RS.EOF
        If Not IsNull(DLookup("type", "msysobjects", "name='Conflict_Search'")) Then
            
            MySQL2 = "INSERT INTO Conflict_Search(clientnumber, matternumber, search, relationship, fullname, oldclientmatternumber) " _
            & "SELECT * FROM conflictquery " _
            & "WHERE search like '*" & RS!party & "*';"
        db.Execute MySQL2, dbFailOnError
        RS.MoveNext
       Else
       db.Execute "CREATE TABLE Conflict_Search(clientnumber Integer, matternumber integer, search char, relationship char, fullname char, oldclientmatternumber char);"
        MySQL3 = "INSERT INTO Conflict_Search(clientnumber, matternumber, search, relationship, fullname, oldclientmatternumber) " _
        & "SELECT * FROM conflictquery " _
        & "WHERE search like '*" & RS!party & "*';"
        db.Execute MySQL3, dbFailOnError
        RS.MoveNext
       End If
    Wend
          
    'print report
    DoCmd.OpenReport "conflict report", acNormal, , , , "New Client or Matter"

    'print report criteria
    DoCmd.OpenReport "conflict report search criteria", acNormal, , , , "New Client or Matter"

    RS.Close
    Set RS = Nothing

    'delete temporary tables
    DoCmd.DeleteObject acTable, "Add_Or_Edit_Conflict_Search"
    DoCmd.DeleteObject acTable, "Conflict_Search"

    DoCmd.Close acForm, "conflictbox"
    End If
    End If
    End If

    End Sub


    Beth Wanner

    Tuesday, June 11, 2013 11:06 PM
  • 1.  Just want to confirm that [old_number] is of Text data-type as per your first post?

    2.  Perhaps, we should test the expression independently from the Report.  In the Query/SQL String being used as the RecordSource for the Report, add a Calculated Column as follows:

    * In grid view:

    Result:IIf(
      Trim([full_name] & "") = "",
      "",
      IIf(
        Trim([old_number] & "") = "",
        [client_number] & "-" & [matter_number],
        [old_number]
         )
       )

    * In SQL view:

    SELECT {other Fields},
    IIf( Trim([full_name] & "") = "", "", IIf( Trim([old_number] & "") = "", [client_number] & "-" & [matter_number], [old_number] ) ) AS Result

    FROM...

    (line-breaks are for posting clarity only and you don't need line-breaks in your grid / SQL).

    Now open the Datasheet view of the Query/SQL and check the values of the Calculated Column [Result]. Do they show the values of [old_number] as per your algorithm or still blank?

    If the Datasheet View works correctly, then you can use the modified Query/SQL as the RecordSource for the Report and bind your TextBox to the Calculated Column (Field) [Result].

          


    Van Dinh


    • Edited by Van DinhMVP Tuesday, June 11, 2013 11:31 PM
    • Marked as answer by BWanner Wednesday, June 12, 2013 12:14 AM
    Tuesday, June 11, 2013 11:23 PM
  • Not what I wanted (I want the SQL String being used as the RecordSource for the problematic Report - either [conflict report] or [conflict report search criteria]), not how the data are collected to be used later in the Report but that's OK in view of my last reply.

    Try the calculated column as described ... If you still have problems then we know the problem is in the data processing (Query/SQL) and not the Report processing.  If the Datasheet of the Query/SQL works as per your algo, then you have a work-around (in my last reply) to overcome the problem in the Report processing. 


    Van Dinh

    Tuesday, June 11, 2013 11:29 PM
  • Van, 

    I think the whole problem stems from the [Report]![Full_name].  I think if I modify my sql to create a query instead of a table and include:

    client_matter_number: IIf( Trim([old_number] & "") = "", [client_number] & "-" & [matter_number], [old_number] ).

    It will all work out. Thanks for pointing me in the right direction.


    Beth Wanner

    Wednesday, June 12, 2013 12:14 AM
  • You're welcome... Glad we could help...

     

    >>[Report]![Full_name]

    If you look back at my posts, you would notice that I removed "[Report]" in my suggested expressions, even in the first suggestion.  I wasn't sure "[Report]" would cause problems but for consistencies with other Field references in the expression, I did not use "[Report]" with [Full_name].

       

    BTW, the last expression has a slightly different and shorter algo. from earlier description... 


    Van Dinh

    Wednesday, June 12, 2013 12:26 AM
  • The plan is to use:

    client_matter_number: IIf( Trim([old_number] & "") = "", [client_number] & "-" & [matter_number], [old_number])

    in my query. And:

    =IIf([Report]![full_name]="","",[client_matter_number])  

    (which I know works)

    as my record source in the report. 

    Thanks again!


    Beth Wanner


    • Edited by BWanner Wednesday, June 12, 2013 12:36 AM
    Wednesday, June 12, 2013 12:35 AM