Results 1 to 13 of 13
  1. #1
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Print bottom border of field rectangle on a report when the field entry is null or blank


    I have a report which shows Genealogy Data in the form of a vertical list - field names along with field contents. The rectangle for the field content is transparent. However when the field contents is blank or null I would like the bottom border of the field rectangle to appear as a line on the report with the other three sides of the field rectangle remaining transparent. This would remind me that I have to search to find this particular piece of data.

    Is there an easy way to do this?

    Thank you.

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,100
    Why not simply use the Nz function to return a long line (made of 10 underscores or so)? Replace the control source of the textbox with Nz([YourField],"______________").
    Cheers,
    Vlad

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Unlike Excel, Access cannot set border visibility individually for each side. You can set textbox border to Invisible then have a string of underscore characters display if field is empty. Can use Nz() function in expression to do that.

    =Nz([fieldname], "____________")

    That assumes an empty field is Null, not an empty string. I don't allow empty strings in text fields, however, if there is a chance of empty string or either, use IIf() instead:

    =IIf([fieldname] & "" = "", "____________", [fieldname])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    I don't allow empty strings in text fields,
    Me neither as a rule. However, I posted about a problem with a multi-field index where one field can be null. Turns out that the solution is to make the potential nulls into empty strings, otherwise the index doesn't work. Maybe you saw that?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    Quote Originally Posted by Gicu View Post
    Why not simply use the Nz function to return a long line (made of 10 underscores or so)? Replace the control source of the textbox with Nz([YourField],"______________").
    Cheers,
    Vlad

    Thank you - this did what I wanted it to accomplish.

    Thank you for responding to this thread.

  6. #6
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    I have created second report and I am trying to use the underscore characters to display if a field is empty. The first option worked on my first report but neither option is working for me on my second report. I am about ready to throw in the towel! I am just an amateur Access user. The options I tried and the error messages I am getting are listed below:

    Option #1

    =Nz([Child First Name],"______________________________")

    Error Message: #Size!

    Option #2

    =IIf([Child Nbr] & ""="","_________________________",[Child Nbr])

    Error Message: #Type!

    Can you spot what I am doing wrong?

    The IIF statement option is particularly confusing to me, but it might be the best bet.

    Thanks for any help.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Either should work. If you want to provide db for analysis, follow instructions at bottom of my post.

    Access reports can be a bit demanding. Some expressions will not calculate if a textbox is not bound to the field. I have had to include bound textboxes and set them as not visible.

    Also, make sure textbox with the expression does not have same name as field used in the expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    IIRC, variant data types return 0 if the field is null, so this might be the cause of the #size error. You could try wrapping the Nz function in a Val function
    Val(Nz([Child First Name],"______"))

    but I think you might still have the error if you're trying to apply this in the Default Value property. If you are using default property, maybe just experiment by moving your expression into the record source as a test (but the control can't be bound), or add a textbox and do it there to see what the result is.

    Another possible cause is that the report has no records. Suggest you run the query that it's based on and apply the same criteria that you used when this error arose and see if you get records. If not, you might have to employ the .HasData property somehow, or move your attempts into report event code rather than in controls.

    As for #Type error, this looks totally wrong: IIf([Child Nbr] & ""="","
    Not sure what you're trying to say with that, but I don't think it translates to IIF([Child Nbr]=,
    but if it does, it makes no sense anyway.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Can certainly concatenate a field or control to an empty string. Empty string represented by two quote marks with nothing between them. Then use that result to compare to empty string. If equal then use the substitute.

    =IIf([fieldname] & "" = "", "__________", [fieldname])

    In VBA:

    If Me.tbxField & "" = "" Then
    do something
    End If

    Or in VBA use constant vbEmptyString
    If Me.tbxField & vbEmptyString = vbEmptyString Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    I misinterpreted that as an attempt to use double quotes around = , maybe as in """=""" or something.

  11. #11
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64

    Nz Function Problems

    Why won't the Nz function and Iff statement work in rpt_Report02A (Input marr001) when the Nz function works in rpt_Report01 and rpt_Report01A?

    Also, why in the query qry_Report2A am I having to input the Marriage Number (marr001) twice?

    Please keep in mind I am a self taught Access user and there probably a number of other issues with my database. I am just using it to document Genealogy information I have and then send out the reports with an underline for missing information to relatives so I can then enter it into a professional Genealogy database.

    Any help you can provide would be appreciated.
    Attached Files Attached Files

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    First thing I noticed is you have autonumber ID field designated as primary key yet you are saving [Marriage Nbr] as key. Suggest you fix this. Also advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

    Textboxes on report have circular reference error. Rename them to something else, just remove spaces from the textbox names.

    Expression uses [Child Number] but the field name is [Child Nbr].

    I deleted the parameter input prompt from query and put it back and no longer get double prompt.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    TINMAN is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    64
    I made changes to the primary key and as you suggested, and I removed all spaces from the names used. I was able to eliminate the double prompt also.

    However, I was not able to make the Nz function or If statement work in the report.

    I was able to solve this problem by using a calculated field in the query and printing it out in the report. The calculated field I used in the query is shown below:

    calChildFirstName: If(IsNull([ChildFirstName]),"________________________________",[ChildFirstName])

    Printing this field in the report did what I was trying to accomplish.

    Thanks for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-28-2016, 11:39 AM
  2. Border not appearing on blank fields - Report
    By jmitchelldueck in forum Reports
    Replies: 1
    Last Post: 08-26-2015, 08:15 AM
  3. Bottom border for a label?
    By crobaseball in forum Access
    Replies: 1
    Last Post: 04-10-2014, 01:52 PM
  4. Replies: 2
    Last Post: 07-24-2013, 12:35 PM
  5. Null field blank
    By brobb56 in forum Reports
    Replies: 3
    Last Post: 09-26-2011, 12:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums