concatenate several fields together AND check for Nulls

C

ChristineMD

Board Regular
Joined
Jul 29, 2008
Messages
53
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

:)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Put this in a public module

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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.:)
 
Upvote 0
I'm not having that problem. :/

You can try changing the one line to
Code: 
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
 
Upvote 0
revans said:
I'm not having that problem. :/

You can try changing the one line to
Code: 
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
Click to expand...


Rich
That did it! You Rock! Thanks so much ! :biggrin: 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! :)
 
Upvote 0
I thought this data was in Access, not Excel?:)
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Challenging VBA Script with Loop, to breakdown text field
Replies
0
Views
533
KDavidP1987
K

Forum statistics

Threads
1,225,477
Messages
6,185,210
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top