Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| 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 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
  #1 (permalink)  
Old January 4th, 2007, 10:50 AM
Authorized User
Join Date: Apr 2006
Location: , , .
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default create one string from various records

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,


  #2 (permalink)  
Old January 4th, 2007, 04:18 PM
pjm pjm is offline
Authorized User
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts

Perhaps someone else could come up with a clever query (or set of queries) to solve this problem, but I would just be inclined to write a loop in VBA code to go thru each of the records and create a brand new table. Just roughing out a bit of pseudo code (I leave it to you to get the syntax right):

       rs = db.recordset("Your table here")
       PrevNum = null
       textline = ""
       for r=1 to nRecords
          if rs("Number") <> PrevNum then
             if PrevNum is not null then
                create new record with PrevNum & textline
             PrevNum = rs("Number")
             textline = rs("text")
             textline = textline & rs("text")
          end if
       next r

You should realize that I am being very sloppy here (don't forget the last [number] in the table). Of course if the table is not sorted by [number] that would be another problem. The simple solution to this is to not use the table itself as input but a query based on the table which is sorted by [number].


Similar Threads
Thread Thread Starter Forum Replies Last Post
automatically create new records scandalous Access VBA 3 February 27th, 2007 03:08 PM
Filtering GridView Records Using a Query String Yubmat BOOK: Beginning ASP.NET 2.0 and Databases 5 November 1st, 2006 09:03 PM
Create Related Records twsinc VB How-To 2 October 31st, 2006 06:51 PM
Create Table's fields from another's Records Jinan BOOK: Beginning Access VBA 0 August 30th, 2004 10:18 PM
I can't create array more than 500000 records. tumarha Pro VB Databases 2 March 1st, 2004 09:30 AM

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