Removing Null Fields from Record? (1 Viewer)

[spacepro]

Hi Guys,

I have a table with about 12 fields and for each record not every field is completed with data. There is a possibility the structure might need to change.

Basically I want to show only the populated fields on that particular record in the textbox in the following format:

Field1,Field5,Field9

Fields 1, 5 and 9 are the populated fields, this changes for each record.

I have tried several things including setting canshrink property on report and controls and putting code to hide if the field isnull, but this doesn't add the commas and doesn't move the fields to centralise the data in the textbox.

for example:

I have tried

[Field1]&","&[Field2] etc etc
but this shows the data as
field1,,,,,,field5,,,,,,,field9

Also tried seperate fields for each field with the canshrink property set , no success.
Need to format the data so it shows as the code above.

Any ideas

Andy

 

[dcx693]

Haven't tried it myself, but have you tried something like:
IIf(IsNull([Field1],"",[Field1]&",")) & IIf(IsNull([Field2],"",[Field2]&","))

 

[spacepro]

Thanks Dcx

It works a treat. Don't know why I didn't use the Iif statement before.

Many Thanks

Andy

 

[dcx693]

Cool. Glad when the theory works in practice!