Remove Space/Gap when a field is blank

  • Thread starter Thread starter samdev
  • Start date Start date
S

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?
 
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
*
 
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 -
Click to expand...

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
 
=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.



samdev said:
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 -
Click to expand...

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
Click to expand...
 
=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 :(
Click to expand...
This is the IIF I have:
Click to expand...
=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])
Click to expand...
I'm I missing something?
Click to expand...
Thx, S- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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!
 
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
Click to expand...

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]
 
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]
Click to expand...

Thx - this works for the most part - :)))
 
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
*
 
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
 
Back
Top