Combining records in access 2007

[barmatt80]

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.

[barmatt80]

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.

[barmatt80]

this is my module code:

Public Function Concat(strField1 As String, _
                       strField2 As String, _
                       strField3 As String, _
                       strField4 As String, _
                       strField5 As String, _
                       strField6 As String, _
                       strField7 As String) As String
 
    Static strLastField1 As String
    Static strCombined As String
 
    If strField1 = strLastField1 Then
        strCombined = strCombined & ", " & strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 _
        & ", " & strField6 & "," & strField7
    Else
    'Correct
        strLastField1 = strField1
        strCombined = strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 & ", " & strField6 _
        & ", " & strField7
    End If
    Concat = strCombined
End Function

and my query is:

SELECT Table1.Field1, Max(ConCat(Field1,Field2,Field3,Field4,Field5,Field6,Field7)) AS COMBINED
FROM Table1
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.

[puppydogbuddy]

this is my module code:

Public Function Concat(strField1 As String, _
                       strField2 As String, _
                       strField3 As String, _
                       strField4 As String, _
                       strField5 As String, _
                       strField6 As String, _
                       strField7 As String) As String
 
    Static strLastField1 As String
    Static strCombined As String
 
    If strField1 = strLastField1 Then
        strCombined = strCombined & ", " & strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 _
        & ", " & strField6 & "," & strField7
    Else
    'Correct
        strLastField1 = strField1
        strCombined = strField2 & ", " & strField3 & ", " & strField4 & ", " & strField5 & ", " & strField6 _
        & ", " & strField7
    End If
    Concat = strCombined
End Function

and my query is:

SELECT Table1.Field1, Max(ConCat(Field1,Field2,Field3,Field4,Field5,Field6,Field7)) AS COMBINED
FROM Table1
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).