Remove Space/Gap when a field is blank

[samdev]

In a subreport of a report, in the detail section I have set a control
to be blank if another field meets certain criteria (using IIF). Works
great BUT in the printed final report, when the field is blank there
is a space in the report. I want the space to be removed when it's
blank.

I have tried "can shrink" and tried various On Format procedures but
none seem to work.

Any ideas?

 

[strive4peace]

Hi Sam,

In your IIF statement, use NULL instead of ""


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[samdev]

Hi Sam,

In your IIF statement, use NULL instead of ""

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*






- Show quoted text -

I just tried that and it didn't work

This is the IIF I have:

=IIf([product id]=1496 Or [product id]=1495 Or [product id]=1497 Or
[product id]=1494 Or [product id]=1493 Or [product id]=1492 Or
[product id]=1491,Null,[product name])

I'm I missing something?

Thx, S

 

[Guest]

=IIf([product id]<>1496 Or [product id]<>1495 Or [product id]<>1497 Or
[product id]<>1494 Or [product id]<>1493 Or [product id]<>1492 Or
[product id]<>1491,[product name])


--
Wayne
Manchester, England.

Hi Sam,

In your IIF statement, use NULL instead of ""

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*






- Show quoted text -

I just tried that and it didn't work

This is the IIF I have:

=IIf([product id]=1496 Or [product id]=1495 Or [product id]=1497 Or
[product id]=1494 Or [product id]=1493 Or [product id]=1492 Or
[product id]=1491,Null,[product name])

I'm I missing something?

Thx, S

 

[samdev]

=IIf([product id]<>1496 Or [product id]<>1495 Or [product id]<>1497 Or
[product id]<>1494 Or [product id]<>1493 Or [product id]<>1492 Or
[product id]<>1491,[product name])

--
Wayne
Manchester, England.

I just tried that and it didn't work

This is the IIF I have:

=IIf([product id]=1496 Or [product id]=1495 Or [product id]=1497 Or
[product id]=1494 Or [product id]=1493 Or [product id]=1492 Or
[product id]=1491,Null,[product name])

I'm I missing something?

Thx, S- Hide quoted text -

- Show quoted text -

This doesn't work as it shows the product name when product id is 1496
etc....I want it so if the product ID is one of the above used in the
above formula, that where the product name should go it doesn't show a
blank line/gap between that product and the next one.....

For example:

This is how it works now - with a blank line where Product 1496 etc
goes - I need it to be blank but without the gap.

Product 1
Product 2

Product 3

This is how I would like it to be:

Product 1
Product 2
Product 3

Sorry for all the confusion but sure appreciate any help, anyone can
offer....

Thx!

 

[John W. Vinson]

This is how it works now - with a blank line where Product 1496 etc
goes - I need it to be blank but without the gap.

Product 1
Product 2

Product 3

On a Report you can set the CanShrink property of the textbox to true so that
it will disappear if its value is NULL.

John W. Vinson [MVP]

 

[samdev]

On a Report you can set the CanShrink property of the textbox to true so that
it will disappear if its value is NULL.

John W. Vinson [MVP]

Thx - this works for the most part - ))

 

[strive4peace]

thanks, John -- guess I neglected to mention that

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

I've also had problems with shrinking spaces when there's no data to show. I
tried all the recommendations on this site until I tried the
following...concatenating the various fields into one unbound field with line
breaks at the end of each field so they list one field per line.

In the Control Source of an unbound text field, insert

=[Fieldname1]+ Chr(13) + Chr(10) &[Fieldname2]+ Chr(13) + Chr(10) & ..etc...

This results in

Fieldname1
Fieldname2
Fieldname5

with no empty line spaces where, eg, Fieldnames 3 and 4 contain no data.

Hope this helps