• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 15
  • 13
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 15
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now