Making "0" lines disappear from an invoice report

[ebasshead]

Hi Everybody,

Firstly thank you for all the help this site has given me over the past year in getting my database to hum, its been awesome.

I'm currently building an itemised invoice report to send to clients, and having a hitch with presentation. At the moment its a collection of individual text boxes showing the number of adults, children, weeks and extra days a family has stayed in a house.

Sometimes 2 people with no children may stay for two weeks and no extra days, when I run the report it will show 2 adults, 0 children, 2 weeks and 0 days.

Is it possible in Access to program one text box, with adults, children, weeks and days on seperate lines of the report, and, if it is 0 children and 0 days, for those lines to disappear completely and for the lines below it to move up.

Cheers in advance

Eddie

[puppydogbuddy]

The easiest way to fix is to add the following constraint to the criteria row of the invoice amount column of the query that is the record source for the report.


<> 0

[ebasshead]

Thank you,

I tried the <>0 in the criteria section of my query, but it will not include invoices that have 0 extra days or 0 kids etc. These invoices will still need to be reported even though there are 0 extra days, 0 kids...

So much for the easy fix, and now for the hard?

Cheers Eddie

[DonRayner]

Hi Everybody,

Firstly thank you for all the help this site has given me over the past year in getting my database to hum, its been awesome.

I'm currently building an itemised invoice report to send to clients, and having a hitch with presentation. At the moment its a collection of individual text boxes showing the number of adults, children, weeks and extra days a family has stayed in a house.

Sometimes 2 people with no children may stay for two weeks and no extra days, when I run the report it will show 2 adults, 0 children, 2 weeks and 0 days.

Is it possible in Access to program one text box, with adults, children, weeks and days on seperate lines of the report, and, if it is 0 children and 0 days, for those lines to disappear completely and for the lines below it to move up.

Cheers in advance

Eddie

You could use an unbound text field to present your data and use something like the formula below as it's data source. Set the height of the field for a single line and set it's "Can Grow" properties to yes.

= ([NumAdults] & " adults") & vbcrlf & _
(IIf (IsNull([NumChildren]),Null,[NumChildren] & " Children")) & vbcrlf & _
([NumWeeks] & " weeks") & vbcrlf & _
([IIf(IsNull([NumDays]),Null,[NumDays] & " Days"))
 

[puppydogbuddy]

Thank you,

I tried the <>0 in the criteria section of my query, but it will not include invoices that have 0 extra days or 0 kids etc. These invoices will still need to be reported even though there are 0 extra days, 0 kids...

So much for the easy fix, and now for the hard?

Cheers Eddie

If you have separate columns in your query for "extra days" and "kids", try putting the <> 0 constraint in the same criteria row for each of those columns instead of the invoice column and see what happens.

[ebasshead]

Thank you Don and Puppy,

Although I've never written VB before I think the vbCrLF is going to be the way to go. Thank you so much for pointing me in the right direction.

Cheers Eddie

[DonRayner]

Thank you Don and Puppy,

Although I've never written VB before I think the vbCrLF is going to be the way to go. Thank you so much for pointing me in the right direction.

Cheers Eddie

Glad I could be of some help Eddie.

Don