Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 11th, 2007, 02:24 AM
Authorized User
 
Join Date: Apr 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Create one string from multiple fields

Hi all,
I have a question related to strings. We have shipments with remarks but these remarks are covered over 10 lines. I dont want to have a dataset with the same shipmentnumber 10 times with the remarks text in 10 records but one line with the shipment number and one field with the complete text.

I have 3 columns called:
[number] [Sequence] [text]
0 0 abc
0 1 def
1 0 ghi
1 1 jkl
1 2 mno

I would like to see another column [textline] with the text joined together but according to this example (taking the group into account).then I will use a Max on sequence to get only one record with the complete tekst. or an alternative which I havent considered what could work maybe better as mine idea.

[number] [Sequence] [tekst] [textline]
0 0 abc abc
0 1 def abcdef
1 0 ghi ghi
1 1 jkl ghijkl
1 2 mno ghijklmno


result wil be:

[number] [textline]
0 abcdef
1 ghijklmno

best regards,

Paul.
 
Old April 11th, 2007, 03:35 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Paul,

You can use ADO to concatenate strings from multiple records.

For example:
Code:
Public Function ConcatRecord(Criteria As Variant, CriteriaFieldName As String, FieldName As String, TableName As String) As String
'Returns a concatenated string based on:
'Choosing records within the "TableName" table based on the "Criteria" for the "CriteriaFieldName" field.
'Data in the field "FieldName" is then concatenated and returned.

    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim retVal As String
    'Set the seperator value to whatever you wish.
    Const SEPERATOR = ", "

    'Check to see if the Criteria value is a string, if so we need to:
    '- Double up any apostrophies to avoid SQL errors.
    '- Encase the string in apostrophies so it is read as a whole string.
    If VarType(Criteria) = vbString Then
        Criteria = Replace$(Criteria, "'", "''")
        Criteria = "'" & Criteria & "'"
    End If

    sql = "SELECT [" & FieldName & "]" & vbCrLf & _
            "FROM [" & TableName & "]" & vbCrLf & _
            "WHERE [" & CriteriaFieldName & "] = " & Criteria

    With rs
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Source = sql
        .Open

        Do While Not .EOF
            'If this is not the first Item, then append a seperator.
            If Not retVal = "" Then retVal = retVal & SEPERATOR

            'Append the Field Value
            retVal = retVal & .Fields(FieldName)

            'Move to the Next Record.
            .MoveNext
        Loop
    End With

    ConcatRecord = retVal
End Function
I tested this value on the following table named "test":
number id textline
----------------------------
1 1 text 1 num 1
1 2 text 2 num 1
1 3 text 3 num 1
2 4 text 1 num 2
2 5 text 2 num 2
1 6 text 4 num 1
3 7 text 1 num 3

With the call:
Code:
ConcatRecord(1,"number", "textline","test")
And it returned:
text 1 num 1, text 2 num 1, text 3 num 1, text 4 num 1

Which was the expected results.

I hope this helps, any questions then please ask.

Regards,
Rob
 
Old April 11th, 2007, 04:10 AM
Authorized User
 
Join Date: Apr 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Rob I had in my example the fields: Number, Sequence and text. where ware these field values in your function. What does the value 1 represents in your example?

 
Old April 11th, 2007, 04:17 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Paul,

If you copy the code above into a module then in your query enter this into the column in the query designer:

Textline:ConcatRecord([number],"number","text","ENTER NAME OF TABLE WHERE REMARKS ARE STORED HERE")

This will create a column in the query with the concatenated remarks.

Ensure you enter the name of the table where the remarks are stored.

Regards,
Rob

 
Old April 11th, 2007, 04:34 AM
Authorized User
 
Join Date: Apr 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rob,

I made a test table with this content:
number id textline
1 1 text 1 num 1
1 2 text 2 num 1
1 3 text 3 num 1
2 4 text 1 num 2
2 5 text 2 num 2
1 6 text 4 num 1
3 7 text 1 num 3

Then I created a query and used the function. Expr1: ConcatRecord(1,[test]![number],[test]![textline],"test")

An error appears: Run time error (80040e10) No value given for one or more required parameters.




 
Old April 11th, 2007, 04:47 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Paul,

The parameters for the function are:
Criteria: The value that you want to filter by.
CriteriaFieldName: the name of the field that is the criteria.
FieldName: The name of the field that you want to concatenate
TableName: The name of the table the field that you want to concatenate is stored in.

So for your test case:
Expr1: ConcatRecord(1,"number","textline","test")

Remember you need to pass the names, not the values of the fields as you did in your example.

I hope this helps.

Rob

 
Old April 11th, 2007, 06:07 AM
Authorized User
 
Join Date: Apr 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Rob,

I have tried all combinations I could think of but the error keeps comming back. Im lost over here.

 
Old April 11th, 2007, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Paul,

Let me get this right in my head:
[li]You have copied the function in my original post into a code module in Access.
[li]You have then created a query with the expression I posted above.

The error message you are receiving is caused by an Invalid parameter.
You need to ensure the values are correct.
The expression I gave you works fine as I have tested it, are you copying it exactly?

Regards,
Rob

 
Old April 11th, 2007, 06:41 AM
Authorized User
 
Join Date: Apr 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rob,
0)I have created a table with 3 fieldnames all Text

number id textline
1 1 Ams
1 2 ter
1 3 dam
2 1 London

1)I have copied the function in a new module in access.
2)I have created a query and with the wizard I selected this function and by dubble clicking populated the function fields.





 
Old April 11th, 2007, 06:59 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Paul,

As I said before, you CANNOT populate the fields via their names, you must copy and past the expression I gave you EXACTLY.
Double clicking the field names does not enter the field names, it enters their values for the current record.

Regards,
Rob





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Form Fields tripster General .NET 2 October 17th, 2007 04:22 AM
How to create calculated fields MacDevv C# 3 August 16th, 2006 09:38 AM
Group on multiple fields leep Crystal Reports 2 May 21st, 2004 01:12 AM
Updating multiple Rows from multiple fields in ASP vdm_nana SQL Server ASP 0 April 1st, 2004 04:26 AM
Multiple Fields Search Helmut Classic ASP Databases 4 July 15th, 2003 02:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.