'Glue' fieldvalues together in one single string

[Sniper-BoOyA-]

Good Morning,

I am working on a report. The report as a query as a source, and from that query i would like to use the following data:

PercentageAsphalt
PercentageSand
PercantageBrokenStab
PercantageCement

These fields represent the composition of a sample used in the lab.

At this point, they have the following value:

PercentageAsfalt PercantageSand PercentageBrokenStab PercentageCement
------------------------------------------------------------------------------
............0................ 100......................... 0............................. 4

When i present these fields on my form, it will look like this:


............0 % + ............100 % + ................0 % + .....................4 %

Which looks messy. Therefore i would like to hide the labels if the value of the field is 0.

Lets say, PercentageAsfalt is 0. Then i have to hide the field, the "%" and the "+".

If i do that for Percentage BrokenStab aswell, the report will look like this:


....................................100 % +..............................................4%

A lot of empty spaces, which is not ideal.

My question is:

Is it posible to look at the value of the fields, if not 0 then put it in a String (with the "%"and the "+"), and finally add one single textbox or label on the report, and say

me.textbox = string ?

Or is there an other way to do this?

Thanks in advance!

 

[vbaInet]

You don't need separate labels. Just do it all in the same Control Source:

=IIF(Nz([PercentageAsphalt],0)=0,[PercentageAsphalt], [PercentageAsphalt] & " % +")

This won't affect any Sums or Counts you have at the footer.

I was going to suggest using the Format property but you can't set the percent symbol as a literal character.

 

[Sniper-BoOyA-]

Can i do this for all 4 of the fields in one single label though?

Like this?

=IIF(Nz([PercentageAsphalt],0)=0,iif(NZ(PercentageSand,0)=0;Iif(Nz(PercentageBrokenStab,0)=0;iif(Nz(PercentageCement,0)=0;""; [PercentageAsphalt] & " % +" & [PercentageSand] & " % +"& [PercentageBrokenStab] & " % +" & [PercentageCement] & " %"))))

update:

Doesnt work.. Which is understandable because the first PercentageAsphalt has 0 as a value so it doesnt do the rest.

 

[CBrighton]

Using the above example of 0, 100, 0, 4 what exactly would you want the string to display?

 

[vbaInet]

Can i do this for all 4 of the fields in one single label though?

Like this?

=IIF(Nz([PercentageAsphalt],0)=0,iif(NZ(PercentageSand,0)=0;Iif(Nz(PercentageBrokenStab,0)=0;iif(Nz(PercentageCement,0)=0;""; [PercentageAsphalt] & " % +" & [PercentageSand] & " % +"& [PercentageBrokenStab] & " % +" & [PercentageCement] & " %"))))

update:

Doesnt work.. Which is understandable because the first PercentageAsphalt has 0 as a value so it doesnt do the rest.

You will have problems with alignment.

 

[Sniper-BoOyA-]

Using the above example of 0, 100, 0, 4 what exactly would you want the string to display?

Well.. I only want to display the fields if they are not 0.

So lets say instead of presenting the following on a form:

PercAsphalt PercSand PercBrokenStab PercCement
---------------------------------------------------
......0 % + ...100%+ .... 0 & + .....4 %

I would like to present

PercSand PercCement
----------------------
....100% .+ .4 &

But then again, if another project has the following results:

PercAsphalt PercSand PercBrokenStab PercCement
---------------------------------------------------
....15 % + .....85%+ ...........0 & ...........+ 6 %

I would like to present

PercAsphalt PercSand PercCement
------------------------------------
....15% .+ ......85%+ .......6 &

 

[CBrighton]

Setting the visible property on a control to false if it holds a set value (or a null value if you prefer) is simple.

Having the VBA then alter the Left property of each control to move them all next to each other and remove all gaps is more complex, because there are so many possible variations on the result.

 

[vbaInet]

You will still have problems with alignment if you consider concatenating.

Also, if you have more than one record then what you're trying to do won't be logical unless the 0 is consistent throughout that field in all the records.