By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Combining records in access 2007

P: 55
I just found this site, and have found some great information. My question is in reguards to this post here: http://www.thescripts.com/forum/thread569535.html

My question is this, First can this be used if the table has 7 columns(named Field1 through Field7). Secondly if so How?

I am really new to vb, and understand some, but not much. I appreciate the help and look forward to contributing to this forum once i get more knowledge. and training.

Thanks in advance.
Dec 26 '07 #1
Share this Question
Share on Google+
3 Replies


P: 55
I just found this site, and have found some great information. My question is in reguards to this post here: http://www.thescripts.com/forum/thread569535.html

My question is this, First can this be used if the table has 7 columns(named Field1 through Field7). Secondly if so How?

I am really new to vb, and understand some, but not much. I appreciate the help and look forward to contributing to this forum once i get more knowledge. and training.

Thanks in advance.

I figured it out, took some time, but in the end wasn't hard at all just some simple changes. I'll post my code up later to help others that want to perform this with multiple fields.
Dec 27 '07 #2

P: 55
this is my module code:
Expand|Select|Wrap|Line Numbers
  1. Public Function Concat(strField1 As String, _
  2.                        strField2 As String, _
  3.                        strField3 As String, _
  4.                        strField4 As String, _
  5.                        strField5 As String, _
  6.                        strField6 As String, _
  7.                        strField7 As String) As String
  8.  
  9.     Static strLastField1 As String
  10.     Static strCombined As String
  11.  
  12.     If strField1 = strLastField1 Then
  13.         strCombined = strCombined & ", " & strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 _
  14.         & ", " & strField6 & "," & strField7
  15.     Else
  16.     'Correct
  17.         strLastField1 = strField1
  18.         strCombined = strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 & ", " & strField6 _
  19.         & ", " & strField7
  20.     End If
  21.     Concat = strCombined
  22. End Function
and my query is:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Field1, Max(ConCat(Field1,Field2,Field3,Field4,Field5,Field6,Field7)) AS COMBINED
  2. FROM Table1
  3. GROUP BY Table1.Field1;
The problem is once those fields are concatenated they are larger than 255 characters. So is there anyways around that? I am sure a memo field. Maybe using append table after making the table correctly using a memo field? Error I get is Data type mismatch in criteria expression? Any help?

Thanks.
Dec 27 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
this is my module code:
Expand|Select|Wrap|Line Numbers
  1. Public Function Concat(strField1 As String, _
  2.                        strField2 As String, _
  3.                        strField3 As String, _
  4.                        strField4 As String, _
  5.                        strField5 As String, _
  6.                        strField6 As String, _
  7.                        strField7 As String) As String
  8.  
  9.     Static strLastField1 As String
  10.     Static strCombined As String
  11.  
  12.     If strField1 = strLastField1 Then
  13.         strCombined = strCombined & ", " & strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 _
  14.         & ", " & strField6 & "," & strField7
  15.     Else
  16.     'Correct
  17.         strLastField1 = strField1
  18.         strCombined = strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 & ", " & strField6 _
  19.         & ", " & strField7
  20.     End If
  21.     Concat = strCombined
  22. End Function
and my query is:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Field1, Max(ConCat(Field1,Field2,Field3,Field4,Field5,Field6,Field7)) AS COMBINED
  2. FROM Table1
  3. GROUP BY Table1.Field1;
The problem is once those fields are concatenated they are larger than 255 characters. So is there anyways around that? I am sure a memo field. Maybe using append table after making the table correctly using a memo field? Error I get is Data type mismatch in criteria expression? Any help?

Thanks.
Check the data types of the fields in your table. One or more may be defined as other than the text(string) data type used in your code. If you find for example, field3 is numeric, one way around is to use an Access function to convert the data type in your code....CStr(field3).
Jan 5 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.