SQL with strVarible

[silentwolf]

Hi guys,

something simple for you I am pretty sure )

    Dim db As DAO.Database
    Dim tbd As DAO.TableDef
    Dim strSQL As String
    Dim strTable As String
    
    strTable = "Neu"
    
    Set db = CurrentDb
            
    Set tbd = db.TableDefs(strTable)
    
'this is ok when I use the Query Builder...
    strSQL = "UPDATE Neu SET Neu.Zahlungsreferenz = [Neu].[ZahlRef] " & _
                "WHERE (((Neu.Zahlungsreferenz)=""x"") AND ((Neu.ZahlRef)<>""""));"

'How this need to be changed to be able to use the strTable Varible?            
    strSQL = "UPDATE " & strTable & " SET [Zahlungsreferenz] = [ZahlRef] " & _
              "WHERE (([Zahlungsreferenz])=""x"") AND [ZahlRef]<>""));"

    db.Execute strSQL
    
    Set db = Nothing

Can someone please let me know how does this code need to be changed the strSQL with the strTable varible?

Thanks for your help )

 

[xenou]

if you look at the very end of your string you have the quotes handled differently:

strSQL = "UPDATE Neu SET Neu.Zahlungsreferenz = [Neu].[ZahlRef] " & _
"WHERE (((Neu.Zahlungsreferenz)=""x"") AND ((Neu.ZahlRef)<>""""));"

'How this need to be changed to be able to use the strTable Varible?
strSQL = "UPDATE " & strTable & " SET [Zahlungsreferenz] = [ZahlRef] " & _
"WHERE (([Zahlungsreferenz])=""x"") AND [ZahlRef]<>""));"

You probably just want to start over with the original string and only change the table part at the beginning.

 

[xenou]

Note:
also this would work (using sinqle quotes inside the double quotes):

strSQL = "UPDATE " & strTable & " SET [Zahlungsreferenz] = [ZahlRef] " & _
"WHERE (([Zahlungsreferenz])='x') AND [ZahlRef]<>''));"

 

[silentwolf]

HI Xenou,
many thanks I will try that tomorrow but I am sure all will be fine
Thank you!

 

[silentwolf]

Hi,
Xenou I did try that but I do still get an error .. 3075 ..
somehow I still struggle with this SQL

 

[xenou]

Hi it looks like you need to fix parentheses too.

 

[silentwolf]

Sub UpdateTest()
Dim db As DAO.Database
Dim tbd As DAO.TableDef
Dim strSQL As String
Dim strTable As String

strTable = "Neu"

Set db = CurrentDb

Set tbd = db.TableDefs(strTable)

''this is ok when I use the Query Builder...
' strSQL = "UPDATE Neu SET Neu.Zahlungsreferenz = [Neu].[ZahlRef] " & _
' "WHERE (((Neu.Zahlungsreferenz)=""x"") AND ((Neu.ZahlRef)<>""""));"

strSQL = "UPDATE " & strTable & " SET [Zahlungsreferenz] = [ZahlRef] " & _
"WHERE (([Zahlungsreferenz])='x') AND [ZahlRef]<>''));"

db.Execute strSQL

Set db = Nothing

End Sub

This is my current code

 

[xenou]

Hi, you have two opening parentheses and four closing parentheses. That's trouble.

 

[JonXL]

What's wrong with this?

strSQL = "UPDATE " & strTable & " SET Zahlungsreferenz = [ZahlRef] " & _
               "WHERE Zahlungsreferenz = ""x"" AND ZahlRef <> """";"

 

[welshgasman]

What's wrong with this?

strSQL = "UPDATE " & strTable & " SET Zahlungsreferenz = [ZahlRef] " & _
               "WHERE Zahlungsreferenz = ""x"" AND ZahlRef <> """";"

Noy sure, but a Debug.Print normally gives it away?