faris
asked on
Hiding fields in a report if null
My table has many fields used for costing.
I want to be able to print an invoice (report) automatically
but I do not want to display it if the value of the field is zero or null.
How can I do this?
I want to be able to print an invoice (report) automatically
but I do not want to display it if the value of the field is zero or null.
How can I do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Greetings, faris,
Stefaans answer and EvanLs comment are both correct, so you have to choose according to the function you need. Do you wish to display the field for some records, and not for others, go with Stefaans answer. Do you wish to hide the entire column of fields for all records based on the null criteria of a single record, then EvanLs comment is your solution.
Imagine!
Stefaans answer and EvanLs comment are both correct, so you have to choose according to the function you need. Do you wish to display the field for some records, and not for others, go with Stefaans answer. Do you wish to hide the entire column of fields for all records based on the null criteria of a single record, then EvanLs comment is your solution.
Imagine!
Omega,
You are correct. If there are only some cases where the field shouldn't be displayed, then my answer will be the best one, though in the other case it will also work.
Best regards,
Stefaan
You are correct. If there are only some cases where the field shouldn't be displayed, then my answer will be the best one, though in the other case it will also work.
Best regards,
Stefaan
Even better solution, Faris:
In this case, I put this code right into the report text field's Control Source.
If the field called "PENum" is null, I make the field's value be, "No PE Number Provided." If there IS a value, the value in the database is put in the textbox. You could do the same thing, but instead of writing like I did, "No PE Number Provided," you could simply make the field "" (blank, or empty).
Here's the code:
=IIf([PENum] Is Null,"No PE Number Provided",[PENum])
This is by far the easiest way to accomplish what you want to do. No VB module required.
Try it! It works great!
In this case, I put this code right into the report text field's Control Source.
If the field called "PENum" is null, I make the field's value be, "No PE Number Provided." If there IS a value, the value in the database is put in the textbox. You could do the same thing, but instead of writing like I did, "No PE Number Provided," you could simply make the field "" (blank, or empty).
Here's the code:
=IIf([PENum] Is Null,"No PE Number Provided",[PENum])
This is by far the easiest way to accomplish what you want to do. No VB module required.
Try it! It works great!
Whoops.. Forgot to mention, that this method hides individual text fields in a report, NOT an entire column. I believe this is what your original question was in the first place..
An eay way to deal with nulls is to convert them using the Nz function.
In your report, you could use the following as the control source, where, as an example, the field Age is to be displayed (or not displayed)
=IIf(Nz([Age],0)=0,"",[Age
If Age is null, Nz will convert it to 0
other wise it evaluates to the number in the field
Any 0 (or null) will then be displayed as an empty string, while any other value will be displayed.
You can use this approach anywhere else you want to convert a null; just replace 0 (in the example above) to whatever you want null to be replaced.
This is very useful in SQL lookups, where nulls generate run time errors.
In your report, you could use the following as the control source, where, as an example, the field Age is to be displayed (or not displayed)
=IIf(Nz([Age],0)=0,"",[Age
If Age is null, Nz will convert it to 0
other wise it evaluates to the number in the field
Any 0 (or null) will then be displayed as an empty string, while any other value will be displayed.
You can use this approach anywhere else you want to convert a null; just replace 0 (in the example above) to whatever you want null to be replaced.
This is very useful in SQL lookups, where nulls generate run time errors.
This question was awarded, but never cleared due to the JSP-500 errors of that time. It was "stuck" against userID -1 versus the intended expert whom you awarded. This corrects the problem and the expert will now receive these points; points verified.
Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.
This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.
This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
Rough example:
Sub MyReport_OnOpen()
If FieldName < 1 Then
FieldName.Visible = False
Else
FieldName.Visible = True
End Sub
I've done this before I believe and think it works okay..