Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Post Unanswered: Make it pretty! Report question...

    I have created a very neat report.

    I would like to add a row of data to this report and keep it neat.

    The row of data may consist of up to 16 fields, depending on if there is data in them or not. I used the following:

    = [field1] &", "& [field2] &", "& [field3]....

    But that sticks the comma there even if there is not any data! (duh!)

    I would like these fields to appear only if there is data in them and also to have a comma separate each one. If there is no data, I do not want a comma to appear.

    Sounds like a big IIf statement?

    muchos gracias!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think an IIF statement would be apropriate here. How many times will this field be calculated? Will this appear in the detail section (or other repeating) section of your report, or will this be a header/footer?

    If it is a header/footer, I would suggest using a for loop to evaluate null, and assign a string to a global var accordingly, then use a label instead of a text box, and set the caption property to the variable.

    Something along the lines of:

    Dim i As Integer
    Dim StringVar As String

    For i = 0 to 15
    if ISNULL(me.recordset.fields(i)) Then
    StringVar = StringVar & ", "
    End If
    Next i

    lblString = String Var

    End Sub

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Cool Re: Make it pretty! Report question...

    Originally posted by jimmyswinger
    I have created a very neat report.

    I would like to add a row of data to this report and keep it neat.

    The row of data may consist of up to 16 fields, depending on if there is data in them or not. I used the following:

    = [field1] &", "& [field2] &", "& [field3]....

    But that sticks the comma there even if there is not any data! (duh!)

    I would like these fields to appear only if there is data in them and also to have a comma separate each one. If there is no data, I do not want a comma to appear.

    Sounds like a big IIf statement?

    muchos gracias!
    You can make a Query in which you can optimize your Fileds:

    FieldNO1: IIf(IsNull([Filed1]),"",Field1)FieldNO":
    FieldNO2: IIf(IsNull([Filed2]),"",Field")


    In your report, set its RecordSource to that Query

    GoodLuck

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can make a Query in which you can optimize your Fileds:

    FieldNO1: IIf(IsNull([Filed1]),"",Field1)FieldNO":
    FieldNO2: IIf(IsNull([Filed2]),"",Field")


    In your report, set its RecordSource to that Query

    GoodLuck
    That was a much better idea!

    I guess mine was a bit like cutting string with a chainsaw. However, there will need to be one very minor alteration whereas the issue is the "," appearing/not appearing.

    IIF(IsNull([field1]), "", ", " + Field1)

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    That was the fine tuning...

    Good

  6. #6
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Talking

    That was fantastic! i love the way you guys work. Maybe you can start a company!!

    So, yes, the data was to go into the detail section of a report. here is what I did:

    1) Created a new query
    2) added all the [PROVNO##] fields to the query
    3) for criteria, I added the following (modified for that fieldname);

    IIf(IsNull([provno1]),"",", "+"provno1")

    and so on until i had entered criteria for all sixteen fields.

    The query came up blank. I toyed around with it but to no avail. I see the wonderful logic you guys are suggesting but as of yet, I cannot make it work.

    Any suggestions?

    thanks and thanks again

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You should be adding the IsNull statement to the text box, not the query criteria silly!!

    Also, there should not be quotes around "provno1", whereas that's going to return a string instead of the field value.

    so, try taking the criteria out of your query, and use this in a text box:

    =IIf(IsNull([provno1]),"",", "+[provno1])

    Make it work with only one field first, that's the simplest way. It eliminates the "oops, I misplaced one comma on the 14th field" errors that I'm absolutely famous for.

  8. #8
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    DOH! I'm a total hack!

    I am thrilled and delighted at this solution!

    Many thanks, your assistance is most appreciated!!

  9. #9
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Here is what I ended up doing:

    I added

    =IIf(IsNull([provno1])," ",[provno1]) & IIf(IsNull([provno2])," ",", " & [provno2]) & etc... etc...

    to a textbox on the report. I did not have to create a new query!!

    Thanks again for all your help!

  10. #10
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Oh yeah, the only problem is that if there are no records for that report, this statement generates and error?! (if the query the report is based on contains no records it errors on this text box)

    it says #Error instead of leaving it blank.

    ! Any ideas?

Posting Permissions

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