Problem concatenating one to many in a single column

[DrSmyth]

Hi,

I've been trying to concatenate a one to many relationship in a single column by using faq701-3499. But I'm getting an error message runtime error 3075 when i get to the line:

Set rs1 = db.OpenRecordset(sql)

it references the line

sql = "select Note_Txt from OutstandingEstatesbyPSM2 where Esta_Cod = '" & rs!Esta_Cod & ""

where Note_Txt is my many field OutstandingEstatesbyPSM2 is my table and Esta_Cod is my unique identifier...

can anybody help?, I've listed the full code below and commented where the error is appearing:

Sub MakeOutstandTexttbl()
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim vStore As String

Set db = CurrentDb


db.Execute "Delete Temptable.* from temptable;"


db.Execute ("insert into TempTable(Esta_Cod) select distinct Esta_Cod from OutstandingEstatesbyPSM2")
Set rs = db.OpenRecordset("select distinct Esta_Cod from TempTable")

Do Until rs.EOF = True

    vStore = ""
    
    Dim sql As String
    sql = "select Note_Txt from OutstandingEstatesbyPSM2 where Esta_Cod = '" & rs!Esta_Cod & ""

                Set rs1 = db.OpenRecordset(sql) ' code breaks here with run time error 3075 syntax error in string in query expression 'Esta_Cod = 'DN1#0001AB'

        
        Do While rs1.EOF = False
            vStore = vStore + "," + rs1!store
            rs1.MoveNext
        Loop
    If Len(vStore) > 0 Then

    vStore = Right(vStore, Len(vStore) - 1)
    

    db.Execute ("update temptable set Note_Txt + '" + vStore + "' where Esta_Cod = '" & rs!Esta_Cod & "")
    
    Else

    End If

    rs.MoveNext
    Loop

    Set rs = Nothing
    Set rs1 = Nothing
    Set db = Nothing
    
End Sub

 

[lespaul]

db.Execute ("insert into TempTable(Esta_Cod) select distinct Esta_Cod from OutstandingEstatesbyPSM2")
[COLOR=red] you really don't need the distinct in the query below because you only put distinct Esta_Cod values in the tempTable[/color]
Set rs = db.OpenRecordset("select distinct Esta_Cod from TempTable")

is this one of your Esta_Cod values? 'DN1#0001AB'



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[DrSmyth]

Yes DN1#0001AB is one of my Esta_cod. I've removed the distinct from where you recommended, but getting same error message (sorry, didn't know if this was a suggested solution or just a recomendation for cleaning up the code)

Thanks anyway

 

[HarleyQuinn]

Don't know if this is the problem but you are missing off a single quote:

sql = "select Note_Txt from OutstandingEstatesbyPSM2 where Esta_Cod = '" & rs!Esta_Cod & "[red]'[/red]"

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[lespaul]

You've got the wrong tick mark here:

sql = "select Note_Txt from OutstandingEstatesbyPSM2 where Esta_Cod = '" & rs!Esta_Cod & [COLOR=red]"[/color]"

try:

sql = "select Note_Txt from OutstandingEstatesbyPSM2 where Esta_Cod = '" & rs!Esta_Cod & "[COLOR=red]';[/color]"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[DrSmyth]

Hi HarleyQuin,

Thanks for the advice, your suggestion seems to get me past the initial error, but now i'm stopping on the line

 db.Execute ("update temptable set Note_Txt + '" + vStore + "' where Esta_Cod = '" & rs!Esta_Cod & "'")

i'm getting a runtime error 3144 - syntax error in update statement.. Any suggestions???

 

[DrSmyth]

Hi lespaul,

Just tried your suggestion, which is doing the same as harleyquins... again, it's breaking at the same point... Thanks for the help so far guys... Been tearing my hair out over this one...

 

[HarleyQuinn]

Try

 db.Execute ("update temptable set Note_Txt [red]=[/red] '" [red]&[/red] vStore [red]&[/red] "' where Esta_Cod = '" & rs!Esta_Cod & "'")

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[DrSmyth]

Hi Harleyquin, that starts to run just great.... Thanks everso for the help...

Just one more thing though (I'm starting to sound like Detective Columbo now!!!) it runs for a bit then breaks on

   vStore = vStore + "," + rs1!Note_Txt

with an invalid use of null error... Would this be down to my data? I've looked through all the notes for this item and they all seem to have a value...

 

[HarleyQuinn]

Hi DrSmyth

Could you tell me where in the code this snippet is from and possibly show me a fuller example to allow me to see if anything else could be causing this?

From what you have said so far I would still try something like this to allow you to skip any NULL data:

If Not IsNull(rs1!Note_Txt) then vStore = vStore & "," & rs1!Note_Txt

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[DrSmyth]

Hi HarleyQuinn,

This is the full code, I've tried your snippet in place of my existing code (which I've commented out before yours)

Sub MakeOutstandTexttbl()
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim vStore As String

Set db = CurrentDb


db.Execute "Delete Temptable.* from temptable;"


db.Execute ("insert into TempTable(Esta_Cod) select distinct Esta_Cod from OutstandingEstatesbyPSM2")
Set rs = db.OpenRecordset("select distinct Esta_Cod from TempTable")

Do Until rs.EOF = True

    vStore = ""
    
    Dim sql As String
    sql = "select Note_Txt from OutstandingEstatesbyPSM2 where Esta_Cod = '" & rs!Esta_Cod & " ';"
    
        
        Set rs1 = db.OpenRecordset(sql)
 
        
        Do While rs1.EOF = False
     'vStore = vStore + "," + rs1!Note_Txt
         If Not IsNull(rs1!Note_Txt) Then vStore = vStore & "," & rs1!Note_Txt
            rs1.MoveNext
        Loop
    If Len(vStore) > 0 Then
   
    vStore = Right(vStore, Len(vStore) - 1)
    

    db.Execute ("update temptable set Note_Txt = '" & vStore & "' where Esta_Cod = '" & rs!Esta_Cod & "'")
    
    Else

    End If

    rs.MoveNext
    Loop

    Set rs = Nothing
    Set rs1 = Nothing
    Set db = Nothing
    
End Sub

Basically it runs through about 50 records and then breaks on this line. Since changing to your line of code the error message has changed to:

Run time error '3075' Syntax error (missing operator) in query expression "it then shows a non sequential concatenation of the notes fields for this particular Esta_Cod

 

[PHV]

And this ?

If Len(vStore) > 0 Then
    vStore = Replace(Mid(vStore, 2), "'", "''")
    db.Execute ("update temptable set Note_Txt = '" & vStore & "' where Esta_Cod = '" & rs!Esta_Cod & "'")
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DrSmyth]

Hi PHV,

'Replace' is coming up as sub or function not defined (i'm using access '97)....

Andy

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4342

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DrSmyth]

Thanks PHV, That link is really useful.... Well the code seems to run for longer now, however, it still breaks down on the line:

db.Execute ("update temptable set Note_Txt = '" & vStore & "' where Esta_Cod = '" & rs!Esta_Cod & "'")

with the same error message: Run time error '3075' Syntax error (missing operator) in query expression. Do you think that this could be a data validation problem? Or could there be something in the note field that's causing it to break?

 

[DrSmyth]

I've sorted it out. One of the Note_Txt fields had a "|" in it which seemed to stop it running properly. I've done a search through and removed any others which seems to have solved the problem.... Do you know why this was an issue?

Thanks for all the help though..

Dr