Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 11th, 2007, 02:24 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
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.
Reply With Quote
  #2 (permalink)  
Old April 11th, 2007, 03:35 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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
Reply With Quote
  #3 (permalink)  
Old April 11th, 2007, 04:10 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
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?

Reply With Quote
  #4 (permalink)  
Old April 11th, 2007, 04:17 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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

Reply With Quote
  #5 (permalink)  
Old April 11th, 2007, 04:34 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
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.




Reply With Quote
  #6 (permalink)  
Old April 11th, 2007, 04:47 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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

Reply With Quote
  #7 (permalink)  
Old April 11th, 2007, 06:07 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
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.

Reply With Quote
  #8 (permalink)  
Old April 11th, 2007, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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

Reply With Quote
  #9 (permalink)  
Old April 11th, 2007, 06:41 AM
Authorized User
 
Join Date: Apr 2006
Location: , , .
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.





Reply With Quote
  #10 (permalink)  
Old April 11th, 2007, 06:59 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:00 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.