Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Groupheader Report (A2k)

    I run a report which specifies clients sales.
    Some of the data contains no client, so I'm trying to use the on_Format of the report to replace empty fields with a name.
    So

    <pre>Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If [INS_NME] = "" Then
    Me.INS_NME = "Un-Specified Client"
    End If
    End Sub</pre>



    Or is it better to enter a iif statement in the underlying query ?

    <pre>iif([INS_NME]="",[INS_NME]="Un-Specified Client")</pre>


    Whichever way, it doesn't work !!

    Anyone help ?

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Dave:

    Another approach would be to use a calculated field in either a query or the SQL statement upon which the report is based.

    In the formula I use the "Nz" or NullZero function.

    MyIns_Nme: Nz([Ins_Nme],"Un-specified Client")

    -Mike

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Dave:

    Your formula --> iif([INS_NME]="",[INS_NME]="Un-Specified Client")

    I suspect that the problem is that you don't specify the outcome if IIF is false. If [Ins_Name] = "", show "Un-Specified Client". If [Ins_Name] <> "", what should the field display?

    I still prefer the Nz function...

    -Mike

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Thanks Mike, but this doesn't work.

    I tried adapting to :

    Ins Name: iif([INS_NME],"",[INS_NME]="Un-Specified Name")

    Still no go ??

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    I think we posted together there Mike !

    Ok
    Below are the results of the query:
    Where there are blank strings, fields, I want to populate with "Un Specified", only if they are blank.
    Where the fields are populated ( I'ved sprayed over for security ) I want leaving as they are.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Dave:

    In another field, try the following

    iif(len([Ins_Nme]),[Ins_Nme],"Un-Specified Name")

    We're trying to measure the length of the presumed text field. If the field has a length, show the field, otherwise display "Un-Specified Name".

    -Mike

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Groupheader Report (A2k)

    Try:
    Ins Name: iif([INS_NME]="","Un-Specified Name",[INS_NME])

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Sorry Lads

    Still at it, none of them work ?

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Groupheader Report (A2k)

    Tell me Dave, what is in [INS_NME]? Can you go into the immediate window and see what it holds.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Patt

    Nice to speak to you again.

    INS-NME is nothing more than a field holding a client name.
    In this case the names are Insurers.
    The data is pulled from an ODBC link, which firstly pulls all data from the foreign database via a couple of select queries.

    One thing I've noticed just, If I place the cursor in any of the empty results, the cursor rests in the middle of the field.
    Using the Left Arrow button, I can move to the left within the field 35 times !!
    This suggests to me that the field isn't empty although no text is visible !!

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Dave,

    What if you use :
    iif(Len(Trim([INS_NME]))=0 OR IsNull(INS_NME),"Un-Specified Name",[INS_NME])
    Francois

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Groupheader Report (A2k)

    Francois

    I think if I use that !!, it's perfect <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    Thanks for the help.

    BTW

    Glad to see we're both having an UP day !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •