What is wrong with this UPDATE statement?

[Bazsl]

UPDATE Costume AS Cc SET Cc.[Photo File] = (SELECT Cp.Photo.FileName AS FileName


FROM Costume AS Cp WHERE Cc.[Costume Number] = Cp.[Costume Number]);

When I click Run I get a result set with a single column headed Photo File. What I am trying to do is set the ShortTest field Photo File to the value of the FileName field in the attachement field named Photo. Thanks.

[CJ_London]

Pretty sure what you are trying to do here is reference a property - although it can be displayed in a query, it can't be assigned to another field

I suspect you will need to use vba to reference the property

part of the problem is [photo file] is one field/one record, but you might have many attachments for that one record. You may know you only have one per record, but access can't assume that.

In vba try something like

dim rst as dao.recordset

set rst=currentdb.openrecordset("SELECT [Costume Number], Photo.FileName as FName FROM Costume")
While not rst.eof
    currentdb.execute ("UPDATE Costume SET [Photo File]='" & nz(rst!FName) & "' WHERE [Costume Number]=" & rst![Costume Number])
    rst.movenext
wend

if there is more than one attachment, only one of them will populate the photo file field. If you have more, then to code becomes more complex.

note having spaces in field and table names is a bad idea, recommend you lose them

[Bazsl]

Thanks Ajax. What does the nz(...) function do. I am very new to vba and have not seen that construct before.

[CJ_London]

learn to google! a tip is to precede your search with 'access vba' i.e. access vba nz function

what it does is if the first value is null, it uses the second value (which if left blank it uses either a zero length string for text or 0 for numbers depending on the first value datatype). It is a standard way of protecting against nulls - the alternative in this situation would be

iif(isnull(rst!FName),"",rst!FName)

I used it here because if there is no attachment, a null would be returned and the code could fail.

[AAAndy]

Pretty sure what you are trying to do here is reference a property - although it can be displayed in a query, it can't be assigned to another field

I suspect you will need to use vba to reference the property

part of the problem is [photo file] is one field/one record, but you might have many attachments for that one record. You may know you only have one per record, but access can't assume that.

In vba try something like

dim rst as dao.recordset

set rst=currentdb.openrecordset("SELECT [Costume Number], Photo.FileName as FName FROM Costume")
While not rst.eof
    currentdb.execute ("UPDATE Costume SET [Photo File]='" & nz(rst!FName) & "' WHERE [Costume Number]=" & rst![Costume Number])
    rst.movenext
wend

if there is more than one attachment, only one of them will populate the photo file field. If you have more, then to code becomes more complex.

note having spaces in field and table names is a bad idea, recommend you lose them

I modified the above code to use in my project but it gives an error.
the error is somewhere in this line CurrentDb.Execute ("UPDATE dbo_tbl_Custom_Subscription_Subscriptions SET [varExtensionSettingValueList]='" & Nz(rst!LStringEmail) & "' WHERE [varReportTitle]=" & rst![varReportTitle])

Does anyone know what the problem is?


Query is:
Dim rst As dao.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT [varReportTitle] FROM dbo_tbl_Custom_Subscription_Subscriptions")

While Not rst.EOF
CurrentDb.Execute ("UPDATE dbo_tbl_Custom_Subscription_Subscriptions SET [varExtensionSettingValueList]='" & Nz(rst!LStringEmail) & "' WHERE [varReportTitle]=" & rst![varReportTitle])
rst.MoveNext
Wend


Error is:
Run-time error '3075':
Syntax error (missing operator) in query expression '[varReportTitle]=Weekly Tuesdays 5pm'.

[orange]

Currentdb.execute does not need brackets around arguments.

Create a string variable for your SQL

eg Dim mySql as string
mySQL = "UPDATE dbo_tbl_Custom_Subscription_Subscriptions SET [varExtensionSettingValueList]='" & Nz(rst!LStringEmail) & "' WHERE [varReportTitle]=" & rst![varReportTitle])
Then do
Debug.print mySQL 'to see how it is rendered
Always test the sql before trying to execute.

[ssanfu]

@AAAndy,

By posting your question in Bazsl's post, you have done what is termed "Hi-Jacking" a thread. You should have started your own thread and referenced this thread.
You would probably get more reads if you had your own thread.


However, there were several things wrong with your code.
Compare this to your version:

    Dim rst As dao.Recordset
    Dim sSQL As String

    
    sSQL = "SELECT [varReportTitle], LStringEmail FROM dbo_tbl_Custom_Subscription_Subscriptions"
    Set rst = CurrentDb.OpenRecordset(sSQL)
    '   MsgBox rst![varReportTitle]
    While Not rst.EOF

        sSQL = "UPDATE dbo_tbl_Custom_Subscription_Subscriptions"
        sSQL = sSQL & " SET [varExtensionSettingValueList] ='" & Nz(rst!LStringEmail) & "' WHERE [varReportTitle] = '" & rst![varReportTitle] & "';"
        Debug.Print sSQL

        CurrentDb.Execute sSQL, dbFailOnError
        rst.MoveNext
    Wend

    'clean up
    rst.Close
    Set rst = Nothing