Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: concatenate related text

    here's a question received by e-mail (sorry John, i don't reply any more) ...and an attempted answer:

    I generate a mailing list of contacts and companies for a particular date. The company ID appears many times - depending on how many contacts form that company are present. How do I generate a table (a datasheet) with two fields - company id (appearing once), string of those contacts mailed for that company id?

    Code:
    private sub tryThis()
    	'requires reference to DAO library 
    	'(set reference in any code window via menu: Tools¦References)
    	
    	'assumptions
    	'table myInput contains: myID as long; myText as string
    	'table myOutput contains: myID as long; myMemo as memo; myDate as date
    
    	
    	dim dabs as dao.database
    	dim reci as dao.recordset
    	dim reco as dao.recordset
    	dim ssql as string
    	dim grab as string
    	dim same as long
    
    	ssql = "SELECT myID, myText FROM myInput ORDER BY myID;"
    	set dabs = currentdb
    	set reci = dabs.openrecordset(ssql)
    	set reco = dabs.openrecordset("myOutput")
    	
    	with reci
    		.movelast
    		.movefirst
    		do while not .eof
    			same = !myID
    			do while !myID = same
    				grab = grab & "," & !myText
    				.movenext
    			loop 'until myID changes
    			reco.addnew
    			reco!myID = same
    			reco!myMemo = mid$(grab,2)
    			reco!myDate = date()
    			reco.update
    			grab = ""
    		loop 'until eof
    	end with
    	set reco = nothing
    	set reci = nothing
    	set dabs = nothing
    end sub
    querying table myOutput by date should produce the desired results.

    izy

  2. #2
    Join Date
    Dec 2002
    Posts
    42

    concatenate relatedtext

    Thanks Izzy.
    The code only finds the id of the first company and concatenates contacts for that one.
    It needs to then continue to loop through the table finding all the other company IDs and concatenating the contacts for each of them.

  3. #3
    Join Date
    Dec 2002
    Posts
    42
    I meant IZYRIDER

  4. #4
    Join Date
    Dec 2002
    Posts
    42
    On closer inspection the code places all but one of the ids into the table.
    An error message occurs "Run time error 3021, No Current record" before the last ID is appended.
    When I step through the code this message occurs on "Do while !My_Id = same" when "same"= lastID.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Red face

    sorry - it's just lousy programming!

    the inner loop (do while !myID = same) takes the recordset EOF after grabbing the last myText for the last myID ...and the whole sad mess collapses.

    a small edit should fix it:

    Code:
    'as before but....
                            same = !myID
    			do while (!myID = same) and (not .EOF) 'there is a space before the ".EOF"
    				grab = grab & "," & !myText
    				.movenext
    			loop 'until myID changes or reci is EOF
    '....and continue as before
    again sorry! izy

  6. #6
    Join Date
    Dec 2002
    Posts
    42

    concatenate relatedtext

    Sorry Izy.
    I get the same error message at the same place - the "AND (NOT. EOF)" doesn't seem to make a difference.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    duhhH

    here's a working .mdb

    i changed the date stamp to a date/time stamp to allow repeated tests on the same day without screwing things up.

    i also changed the line causing the error: access generates an error evaluating the expression ((...EOF) and (!myID.....)) when the myID recordset is EOF. it's obvious when you think about it... i just didn't think.

    izy

  8. #8
    Join Date
    Dec 2002
    Posts
    42

    Smile concatenate relatedtext

    Thanks Izy,
    Works like a charm now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •