Trim in IF statement

[teebird]

Is it possible to use the TRIM function in an IF stratement eg.

="Adult", Sum(IIf([StaturaID] Like "A",1,0)),TRIM [StaturaID])

If the sum for StaturaID is nil - The text box will not be displayed.

Thanks
tee

 

[TomCologne]

TRIM removes spaces, are you trying to show the number of records with [StaturaID] like "A" and only make the txt box visible if the number is greater than 0 or ...?

You're not alone,

TomCologne

 

[teebird]

Yes that is just what I want to do...guess we will both have to wait for someone to solve it for us...

 

[TomCologne]

This qry returns the number of records with "A" as StaturaID, blank, if that number is 0:

SELECT IIf(Count(*)>0,Count(*),"&quot AS Expr1
FROM tblXXX
WHERE (([StaturaID] Like "A&quot);


Maybe you can go from here,

TomCologne

 

[coco86]

OK, I'm not 100% sure exactly what you want to do here but I'm going to take a stab at it.

I'm thinking you want to create a report showing totals for categories, but only categories that have entries.

The recordsource for your report would look like this:

SELECT Sum(IIf([SaturaID] Like "a",1,0)) AS Adult FROM

;

with additional calculated fields for each category

On your form, you would have a text box for each category. The control source for each text box would be something like this:

=IIf([Adult]>0,"Adult: " & [Adult],"&quot

If you want to format the Label you can use two text boxes that are side by side for each category. The first would have a control source of:

=IIf([Adult]>0,"Adult: ","&quot

and the second would have a control source of

=IIf([Adult]>0,[Adult],"&quot

In order to prevent blank spaces appearing for categories with no entries, set the height of each text box to 0 and set the "CanGrow" property to true. Then reduce the space between each text box to a tiny amount.

Hope that helps some.

 

[teebird]

Hi coco86

Thats what I want to do but my report [R - Client VIS]has a number of categories such as below. And if they have no data, they will not be displayed.

Adult
Child
Male
Female

I have them all in a query [Q - Client Data]based on a table [Client Data]

Do you mean the record scource of each control should be like this eg.

=IIf([Adult]>0,"Adult: " & [Adult],"&quot
=IIf([Child]>0,"Adult: " & [Child],"&quot
=IIf([Male]>0,"Adult: " & [Male],"&quot
=IIf([Female]>0,"Adult: " & [Female],"&quot

I did try this but could not be it to work. I have prompt box for the date which came up and then then I had a prompt box for the fields eg.Adult

Thanks for your help.
tee