Update Query VB Code 1

[hext2003]

I am struggling to get the syntax correct on this

Dim SQL1 As String

SQL1 = "Select Ar_Addr.*, csm_addr.* From Ar_Addr RIGHT JOIN csm_addr ON Ar_Addr.key=csm_adr.key"

DoCmd.RunSQL SQL1

I keep getting a runtime error 3135
Syntax error in join operation

any advice?

 

[PWise]

runSql is only for action queries(insert ,update,delete) not for selects

 

[hext2003]

I am trying to do an update query. i have also tried

SQL1 = "UPDATE Ar_Addr FROM Ar_Addr RIGHT JOIN csm_addr ON Ar_Addr.key=csm_addr.key"

still not working.

do you know what I am doing wrong?

 

[hext2003]

SQL1 = "Update Ar_Addr" & " RIGHT JOIN csm_addr" & _
"ON Ar_Addr.Key = csm_addr.key" & _
"SET Ar_Addr.Key = [csm_addr].[key]," & _
"Ar_Addr.Num1 = [csm_addr].[addr_stnumf]," & _
"Ar_Addr.Num2 = [csm_addr].[addr_stnumt]," & _
"Ar_Addr.dir = [csm_addr].[addr_stdir]," & _
"Ar_Addr.street = [csm_addr].[addr_stname]," & _
"Ar_Addr.unit = [csm_addr].[addr_stunit]," & _
"Ar_Addr.city = [csm_addr].[addr_city]," & _
"Ar_Addr.zip = [csm_addr].[addr_zip]"


This didn't work either.

 

[PWise]

SQL1 = "Update Ar_Addr" & " RIGHT JOIN csm_addr" & _'missing space
        "ON Ar_Addr.Key = csm_addr.key" & _ 'missing space
        "SET Ar_Addr.Key = [csm_addr].[key]," & _
        "Ar_Addr.Num1 = [csm_addr].[addr_stnumf]," & _
        "Ar_Addr.Num2 = [csm_addr].[addr_stnumt]," & _
        "Ar_Addr.dir = [csm_addr].[addr_stdir]," & _
        "Ar_Addr.street = [csm_addr].[addr_stname]," & _
        "Ar_Addr.unit = [csm_addr].[addr_stunit]," & _
        "Ar_Addr.city = [csm_addr].[addr_city]," & _
        "Ar_Addr.zip = [csm_addr].[addr_zip]"

try

SQL1 = "Update Ar_Addr" & " RIGHT JOIN csm_addr " & _
        "ON Ar_Addr.Key = csm_addr.key " & _
        "SET Ar_Addr.Key = [csm_addr].[key]," & _
        "Ar_Addr.Num1 = [csm_addr].[addr_stnumf]," & _
        "Ar_Addr.Num2 = [csm_addr].[addr_stnumt]," & _
        "Ar_Addr.dir = [csm_addr].[addr_stdir]," & _
        "Ar_Addr.street = [csm_addr].[addr_stname]," & _
        "Ar_Addr.unit = [csm_addr].[addr_stunit]," & _
        "Ar_Addr.city = [csm_addr].[addr_city]," & _
        "Ar_Addr.zip = [csm_addr].[addr_zip]"

 

[hext2003]

You have GOT to be kidding ME!!! GRRRRRR

Those 2 little spaces made it work!! grrrr

Thank you SOOOO much! I have spent the last 2 hours on this!

Thank you!

 

[PWise]

Well better then working 5 hours on why a query is returning the wrong data and why updates are not working and then noticeing that you are in the wrong database