Null values must show in report list

[Sean O'Halloran]

Dear Forum helpers,

I'm struggling for the words to describe my issue, which is probably why my forum searches haven't been fruitful.

I have to match a pre-designed printed report which looks like this:

Income -- Amount -- Period -- Verification Source
1. from work -- $1000.00 -- month -- paystub
2. from rent -- 0.00
3. from insurance -- 0.00
4. from interest -- 50.00 -- yearly -- bank statement
~
10. from other source -- 0.00

The printed report goes outside my organization, so I can't redesign it. The end "customer" needs the Income values to always appear in the same order, 1 to 10, and to appear whether they are "0.00" or not. Of course, the only two Income records I would want to hold from the example above would be #1 and #4.

Please advise - I would greatly appreciate some terms for what I need so at least I could search for an answer.

Thanks in advance,
Smilin' Sean

 

[Mike375]

Of course, the only two Income records I would want to hold from the example above would be #1 and #4.

You have to be careful with that sort of thing. For example

2. from rent -- 0.00

Having that listed as $0 is quite different to not listing it at all.

The end "customer" needs the Income values to always appear in the same order, 1 to 10

You could add a field for a sort order just like the 1 to 10 list shown in your post.

You could also add a calculated field or textbox that for items where the income was $0 then.......

IIF([Amount]=0,"This person does not receive income from this source",Null)

 

[Sean O'Halloran]

Thank you for your thoughts, Mike.

For my agency's purposes we assume that not having a 'rent' income record means that income from rent is 0.00; so in this case the 'Null' record = 0, which I think leads me to my puzzle: How do I display all 10 items on the printout without having the following negative aspects:

1. Take up extra space on the data-entry screen (already crowded) displaying all 10 items - 2/3 of which will end up $0.00.
2. Make the user enter $0.00.
3. Store those extra records containing $0.00.

The process you suggest would have to be triggered by an existing record with the value of 0.00, correct?

Sean

 

[Mike375]

Sean

For my agency's purposes we assume that not having a 'rent' income record means that income from rent is 0.00; so in this case the 'Null' record = 0, which I think leads me to my puzzle: How do I display all 10 items on the printout without having the following negative aspects:

Based on your first post it appears that you are stuck with a Report format that must list all 10 items?? I can see the need for that as it can come down to if Item 5 etc is Null is that because they do not recieve income form that source or was it a case that they receive income from that source but for the current month it was 0.

1. Take up extra space on the data-entry screen (already crowded) displaying all 10 items - 2/3 of which will end up $0.00.

Perhaps you could have a related form open that is for data entry for the 10 items. Perhaps a button next to each field to insert a 0 where required. Or perhaps have an event run on Lost Focus that inserts a 0 if the field is Null.

2. Make the user enter $0.00.

As above

3. Store those extra records containing $0.00.

Is there a problem storing the extra records? Perhaps you could have a combination of an Append and Delete query run so that after the Report is done the records with 0 are shifted to another table and you put that at the end of the code or macro that opens and prints the Report.

My guess is that the best solution will come your way when you are not even thinking about the problem. I don't think there is any magical code/query etc

Just to recap and to establish if I am reading your correctly (and for any other repliers)

1) Your Report needs to display all 10 items including those where the income is 0

2) But you want to be able to comply with that requirement while at tjhe same time avoiding the creation of records where the income for the item is 0