| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
  
Topic: Field display
Posted: 01 Dec 2009 at 10:58am |
|
I am using CR XI with two MS Access tables. The CR is setup with grids to simulate an Excel spreadsheet. The individual columns contain data from both (2) tables. Most of the rows/columns display data. However, there are several fields that are blank. Is there a formula that I could use to hide all the fields in a row if some are blank?
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Dec 2009 at 11:14am |
You can hide the entire row using the section expert and OR statement for each of the possible conditions
isnull(field1) or isnull(field2) or isnull(field3)...
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 01 Dec 2009 at 11:25am |
|
For clarification, in the section expert, would I put a checkmark in the 'Suppress Blank Section' and then click on the x-2 box next to it for entering these: isnull(field1) or isnull(field2) or isnull(field3)...
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Dec 2009 at 11:33am |
select the section that you want to conditionally suppress (likely the detail section).
You do not need to mark the box just put the formula in the X-2 location next to the "Suppress (no Drill-Down)" option.
The suppress blank section will only suppress if the entiore row is blank, you are trying to suppress the row if only somethings are blank.
If the formula returns a TRUE then it suppresses, if it returns a False it shows.
The formula I gave you assumes that the fields you are checking are actually NULL and not empty. By using OR between it one it will return a TRUE (and suppress) if any of the fields are NULL rather than all of them having to be NULL (if you used AND instead of OR).
If it is possible that soem items are blank instead of NULL and you want to account for that just add that condition to the formula
isnull(table.field1) or table.field1="" or isnull(table.field2) or table.field2="" or isnull(table.field3) or table.field3 ="" Edited by DBlank - 01 Dec 2009 at 11:34am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 01 Dec 2009 at 11:57am |
Trying to follow along with your instructions, while selecting the 'details section' and bringing up the 'section expert', i didn't check 'suppress blank section', but entered this formula in the x-2 next to it:
isnull({tblWilliamsGrantExpenditures.Construction}) or {tblWilliamsGrantExpenditures.Construction}="" or
isnull({tblWilliamsGrantExpenditures.Date}) or {tblWilliamsGrantExpenditures.Date}="" or
isnull({tblWilliamsGrantExpenditures.FundSource}) or {tblWilliamsGrantExpenditures.FundSource}="" or
isnull({tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures}) or {tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures}="" or
isnull({tblWilliamsGrantExpenditures.Payee}) or {tblWilliamsGrantExpenditures.Payee}="" or
isnull({tblWilliamsGrantExpenditures.Planning}) or {tblWilliamsGrantExpenditures.Planning}="" or
isnull({tblWilliamsGrantExpenditures.WarrantNum}) or {tblWilliamsGrantExpenditures.WarrantNum}="" or
isnull({tblWilliamsProjects.ApplicationNo}) or {tblWilliamsProjects.ApplicationNo}="" or
isnull({tblWilliamsProjects.Project}) or {tblWilliamsProjects.Project}="" or
isnull({tblWilliamsProjects.SchoolSites}) or {tblWilliamsProjects.SchoolSites}=""
With this formula, i get this error: A number, or currency amount is required here.
If this helps, the fields that are blank on the CR are blank in the MS Access tables.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 01 Dec 2009 at 11:59am |
When I get the error 'A number, or currency amount is required here', it is highlighting the "" in the first row:
isnull({tblWilliamsGrantExpenditures.Construction}) or {tblWilliamsGrantExpenditures.Construction}="" or
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Dec 2009 at 12:14pm |
the "" can be used for strings
YOu would have to adjust for otehr field types and know what the defualt values are for them.
For example a date or an intifer can never be = "".
An intiger would be =0 or the date would in SQL might be 01/01/1900 instead of NULL.
So you would have to adjust your formula based on the field types for each one.
NULL can apply to prettymuch any field type but the second part would be what you need to fix. I took a guess at it below with changes in red...
isnull({tblWilliamsGrantExpenditures.Construction}) or {tblWilliamsGrantExpenditures.Construction}=0 or
isnull({tblWilliamsGrantExpenditures.Date}) or
isnull({tblWilliamsGrantExpenditures.FundSource}) or {tblWilliamsGrantExpenditures.FundSource}="" or
isnull({tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures}) or {tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures}=0 or
isnull({tblWilliamsGrantExpenditures.Payee}) or {tblWilliamsGrantExpenditures.Payee}="" or
isnull({tblWilliamsGrantExpenditures.Planning}) or {tblWilliamsGrantExpenditures.Planning}="" or
isnull({tblWilliamsGrantExpenditures.WarrantNum}) or {tblWilliamsGrantExpenditures.WarrantNum}=0 or
isnull({tblWilliamsProjects.ApplicationNo}) or {tblWilliamsProjects.ApplicationNo}=0 or
isnull({tblWilliamsProjects.Project}) or {tblWilliamsProjects.Project}="" or
isnull({tblWilliamsProjects.SchoolSites}) or {tblWilliamsProjects.SchoolSites}=0
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 01 Dec 2009 at 12:47pm |
I modified a couple of things based on your example:
isnull({tblWilliamsGrantExpenditures.Construction}) or {tblWilliamsGrantExpenditures.Construction}=0 or
isnull({tblWilliamsGrantExpenditures.Date}) or
isnull({tblWilliamsGrantExpenditures.FundSource}) or {tblWilliamsGrantExpenditures.FundSource}="" or
isnull({tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures}) or {tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures}=0 or
isnull({tblWilliamsGrantExpenditures.Payee}) or {tblWilliamsGrantExpenditures.Payee}="" or
isnull({tblWilliamsGrantExpenditures.Planning}) or {tblWilliamsGrantExpenditures.Planning}=0 or
isnull({tblWilliamsGrantExpenditures.WarrantNum}) or {tblWilliamsGrantExpenditures.WarrantNum}="" or
isnull({tblWilliamsProjects.Project}) or {tblWilliamsProjects.Project}=""
With this formula, I'm not getting any more error messages but here is the problem. The tblWilliamsProjects.Project and the tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures are the only fields that are visible in the rows where I'm trying to hide them. Except for these two fields, all of the other fields are blank in the MS Access tables.
I'm wondering if I'm not linking the tables correctly in CR. The formula you suggested seems to be fine (I don't have error messages), just not working as I hoped.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Dec 2009 at 1:11pm |
Hmmm. I am not exactly sure i am understanding the issue but here is a process.
I usually start to just break these things down and start with the most basic process and test each part to make sure my logic is sound. I then add another part piece and test again until I see where it "broke".
In this case you can create a formula field to return a "Hide" or "Show" and test your process out. Create a formula field, ad dit to your detail section and then add parts and keep looking a the results to see if it is doing waht you want.
Just start with the first part and keep adding.
First test:
if isnull({tblWilliamsGrantExpenditures.Construction}) or {tblWilliamsGrantExpenditures.Construction}=0 then "Hide" else "Show"
Second test:
if isnull({tblWilliamsGrantExpenditures.Construction}) or {tblWilliamsGrantExpenditures.Construction}=0 or isnull({tblWilliamsGrantExpenditures.Date}) then "Hide" else "Show"
repeat as needed.
Does that help?
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 01 Dec 2009 at 1:40pm |
Based on your example, I used the formula and had the following results:
Any field which had, or didnt have data, displayed 'show' or 'hide' in the rows accordingly. This was the same for all fields except for {tblWilliamsProjects.Project} and {tblWilliamsGrantExpenditures.ID-sbfrmWillamsGrantExpenditures} which only displayed 'show'.
|
| IP Logged |
|
|
|
