concatenate several fields together AND check for Nulls

[ChristineMD]

I have a table that has five columns for data, however, not all five columns always HAVE data.

itemNo= 1
Fabric1=48% Cotton
Fabric2=48% Linen
Fabric3=4% Unicorn Fur
Fabric4
Fabric5

So there's an example of a record that has a number of the fabric fields utilized.

I want to combine them all into one field, but separate each data element with something, ideally a comma, but a dash would do. AND I want to EXCLUDE fields that have no data.

So I spent a long time fiddling with iif, but realized I'd never get any more than one result.


There will always be a fabric1
The rest are all maybes


I can easily concatenate all the fields with whatever separator I'd like, but I get a bunch of empty/extra separators at the end, when all fields are not utilized.

Any ideas how to exclude the blank fields? I'm doing this w/ QBE by the way...

Thanks in advance

 

[revans]

Put this in a public module

Public Function StringTogether(ParamArray varItems()) As String
Dim var As Variant
Dim str As String
'rotate through the fields and string em together
For Each var In varItems
  If Not IsNull(var) Then
    str = str & var & ","
  End If
Next
If Len(str) > 0 Then'ditch the last comma
  str = Left(str, Len(str) - 1)
End If
StringTogether = str
End Function

Then call it in the QBE by creating a new column named MyList: StringTogether([Fabric1],[Fabric2],[Fabric3],[Fabric4],[Fabric5])

hth,

Rich

 

[ChristineMD]

Revans, I appreciate your reply! Thank you so much.

This is very nearly working, however, I'm still getting extra separators if there are two or less data elements.

if only fabric1 I get:

100% cotton,,


if fabric1 & fabric2 I get:

95% Cotton, 5 % unicorn fur,


if fabric 1 2 & 3, all is well

I'm on access 2007 if that makes a diff.

Thanks again in advance.

 

[ChristineMD]

Would it be: ??


Public Function StringTogether(ParamArray varItems()) As String
Dim var As Variant
Dim str As String
'rotate through the fields and string em together
For Each var In varItems
If Not IsNull(var) Then
str = str & var & ","
End If
Next
If Len(var) > 0 Then'ditch the last comma
str = Left(str, Len(str) - 1)
End If
StringTogether = str
End Function</pre>

 

[ChristineMD]

Would it be: ??

... no... it is not

 

[Norie]

Christine

Why do you have 5 different fields for the fabrics?

Couldn't you add a field indicating the fabric 'number'? If you did then you probably wouldn't have empty fields.

 

[revans]

I'm not having that problem. :/

You can try changing the one line to

If Not IsNull(var) And Not (var = "") Then

since there might be a null vs empty string issue, perhaps. Meanwhile, I try to recreate your issue.

hth,

Rich

 

[ChristineMD]

I'm not having that problem. :/

You can try changing the one line to

If Not IsNull(var) And Not (var = "") Then

since there might be a null vs empty string issue, perhaps. Meanwhile, I try to recreate your issue.

hth,

Rich

Rich
That did it! You Rock! Thanks so much ! Big happy smile!



Hi Norie - in response to your question, eventually this data/table is going to be converted to XML, and this field will be listed as a bullet point among a number of differing facets, such as laundering instructions, garment fit, length, and then the fabric content, but I wouldn't want each fabric to be a separate bullet point, but it is coming out of excel as separate columns - Thanks for your input, nonetheless!

 

[Norie]

I thought this data was in Access, not Excel?

 

[ChristineMD]

It is in Access, but another department manages the data in excel, and I'm importing into access to manipulate to get ready for xml conversion. tada