combine field results in query grid

I have field1, field2, field3, field4, field5, field6, field7

I have a new field called matcstring

would like totake the results of each field if not null and create a string in matchstring field so:

field1= 2
field2=5
fieldx


matchstring field would be "2,5"

am in access 2010 query grid or a function to do same in vba would be ok
PeterBaileyUkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
...and what is Fieldx?

In a query you would do something like this as a new column/field:

MatchString: Field1 & ", " & Field2 & ", " & Field3 & ", " & .....etc

JeffCoachman
0
PeterBaileyUkAuthor Commented:
if any one of the fields are null then i dont want the empty string or null value with its comma

so i have 7 fields that could have a value if it has a value add to string otherwise do nothing

cannot have
5,6,,8,,,5

for example
0
Jeffrey CoachmanMIS LiasonCommented:
Well, that was not clear from your original post.....

Then do something like this:
MatchString: IIF(Isnull([Field1]),"",[Field1] & ", ") & IIF(Isnull([Field2]),"",[Field2] & ", ")...


0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

PeterBaileyUkAuthor Commented:
Its almost there but not quite I get a commas at the end :
10377,

8430, 7670,
0
Jeffrey CoachmanMIS LiasonCommented:
And use something like this to trim off the trailing ", "

MatchString: Left(IIf(IsNull([Field1]),"",[Field1] & ", ") & IIf(IsNull([Field2]),"",[Field2] & ", ") & IIf(IsNull([Field3]),"",[Field3] & ", "),Len(IIf(IsNull([Field1]),"",[Field1] & ", ") & IIf(IsNull([Field2]),"",[Field2] & ", ") & IIf(IsNull([Field3]),"",[Field3] & ", "))-2)
0
PeterBaileyUkAuthor Commented:
i started in code to try and avoid the comma problem
Public Function createString(Code1 As Variant, Code2 As Variant, Code3 As Variant, Code4 As Variant, Code5 As Variant, Code6 As Variant, Code7 As Variant) As String
Dim TempStr As String
Dim Index As Integer

For Index = 0 To 6




Next Index





End Function

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
In the future, please post examples of the exact output you require for all contingencies...

;-)

JeffCoachman
0
PeterBaileyUkAuthor Commented:
well seems i am failing miserably on the etiquette count, thats why your a genious and me a novice
0
Jeffrey CoachmanMIS LiasonCommented:

Your original request is for this to be done in a query *or* VBA Function.
<am in access 2010 query grid or a function to do same in vba would be ok>
...My post here show the exact output you requested in a Query...
0
Jeffrey CoachmanMIS LiasonCommented:
<well seems i am failing miserably on the etiquette count, thats why your a genious and me a novice>
No problem, I know that sometimes you just post your request, then when you see the result, you realize that it needs to be tweaked.

If your case this was a simple thing to do (even though the syntax look complex...)
it is actually fairly straight forward...
;-)

Jeff
0
PeterBaileyUkAuthor Commented:
I have this have put the real field names in but it doesnt return anything valid says func!

MatchString: Left(IIf(IsNull([CapCode1_1]),"",[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),"",[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),"",[CapCode1_3] & ", "),Len(IIf(IsNull([CapCode1_4]),"",[CapCode1_4] & ", ") & IIf(IsNull([CapCode1_5]),"",[CapCode1_5] & ", ") & IIf(IsNull([CapCode1_6]),"",[CapCode1_6] & ", ")& IIf(IsNull([CapCode1_7]),"",[CapCode1_7] & ", "))-2)

0
Jeffrey CoachmanMIS LiasonCommented:
Can you first confirm that this works in the *Query*
Yes or No?

0
PeterBaileyUkAuthor Commented:
no
0
Jeffrey CoachmanMIS LiasonCommented:
By "In the query" I mean just the raw query, (No functions)
0
PeterBaileyUkAuthor Commented:
returns #Func!
0
PeterBaileyUkAuthor Commented:
yes
0
PeterBaileyUkAuthor Commented:
this is the query so far
SELECT [CapToMvris-CW].MvrisCode, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].CapCode1_2, [CapToMvris-CW].CapCode1_3, [CapToMvris-CW].CapCode1_4, [CapToMvris-CW].CapCode1_5, [CapToMvris-CW].CapCode1_6, [CapToMvris-CW].CapCode1_7, Left(IIf(IsNull([CapCode1_1]),"",[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),"",[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),"",[CapCode1_3] & ", "),Len(IIf(IsNull([CapCode1_4]),"",[CapCode1_4] & ", ") & IIf(IsNull([CapCode1_5]),"",[CapCode1_5] & ", ") & IIf(IsNull([CapCode1_6]),"",[CapCode1_6] & ", ") & IIf(IsNull([CapCode1_7]),"",[CapCode1_7] & ", "))-2) AS MatchString
FROM [CapToMvris-CW];

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
Post a sample of your DB with only 3 fields
(It is a *lot*) easier to troubleshoot 3 fields than 7..
Make sense...
0
PeterBaileyUkAuthor Commented:
Ok as requested
eeex.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
Ahhh I see.

I was using text originally...

So try this as the field in the query grid:
MatchString: IIf(IsNull([CapCode1_1]),Null,[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),Null,[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),Null,[CapCode1_3])

And this is the whole query:
SELECT [CapToMvris-CW].MvrisCode, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].CapCode1_2, [CapToMvris-CW].CapCode1_3, IIf(IsNull([CapCode1_1]),Null,[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),Null,[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),Null,[CapCode1_3]) AS MatchString
FROM [CapToMvris-CW];

Jeff
0
PeterBaileyUkAuthor Commented:
Interestingly Jeff its still returning the commas at the end:

8430, 7670,

8430,



SELECT [CapToMvris-CW].MvrisCode, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].CapCode1_2, [CapToMvris-CW].CapCode1_3, IIf(IsNull([CapCode1_1]),Null,[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),Null,[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),Null,[CapCode1_3]) AS MatchString
FROM [CapToMvris-CW];

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
here is your sample DB back, the query I made works fine and displays the data with no commas...
eeex.accdb
untitled.JPG
0
PeterBaileyUkAuthor Commented:
now thats weird I wonder why mine is not ok one sec i will take a screen shot
0
PeterBaileyUkAuthor Commented:
ok i downloaded the example db and it works but not in my main db the only difference being that the data is linked everything else appears to remain the same

SELECT [CapToMvris-CW].MvrisCode, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].CapCode1_2, [CapToMvris-CW].CapCode1_3, IIf(IsNull([CapCode1_1]),Null,[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),Null,[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),Null,[CapCode1_3]) AS MatchString
FROM [CapToMvris-CW];
ee1.PNG
0
Jeffrey CoachmanMIS LiasonCommented:
OK, try this
eeex-2-.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
ok have amended now for 7 fields and set -2 and now works:

I may need to do this in vba too in other dbs shall I repost based on a vba solution?

SELECT [CapToMvris-CW].MvrisCode, [CapToMvris-CW].CapCode1_1, [CapToMvris-CW].CapCode1_2, [CapToMvris-CW].CapCode1_3, [CapToMvris-CW].CapCode1_4, [CapToMvris-CW].CapCode1_5, [CapToMvris-CW].CapCode1_6, [CapToMvris-CW].CapCode1_7, Left(IIf(IsNull([CapCode1_1]),Null,[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),Null,[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),Null,[CapCode1_3] & ", ") & IIf(IsNull([CapCode1_4]),Null,[CapCode1_4] & ", ") & IIf(IsNull([CapCode1_5]),Null,[CapCode1_5] & ", ") & IIf(IsNull([CapCode1_6]),Null,[CapCode1_6] & ", ") & IIf(IsNull([CapCode1_7]),Null,[CapCode1_7] & ","),Len(IIf(IsNull([CapCode1_1]),Null,[CapCode1_1] & ", ") & IIf(IsNull([CapCode1_2]),Null,[CapCode1_2] & ", ") & IIf(IsNull([CapCode1_3]),Null,[CapCode1_3] & ", ") & IIf(IsNull([CapCode1_4]),Null,[CapCode1_4] & ", ") & IIf(IsNull([CapCode1_5]),Null,[CapCode1_5] & ", ") & IIf(IsNull([CapCode1_6]),Null,[CapCode1_6] & ", ") & IIf(IsNull([CapCode1_7]),Null,[CapCode1_7] & ","))-2) AS MatchString
FROM [CapToMvris-CW];

Open in new window

0
PeterBaileyUkAuthor Commented:
thank you
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.