Using IIF statement in update VBA code

[CBrits]

Hi
I am trying to update a column in a table based on values of other fields in the table. When a create a select statement this works but as soon as I turn it to an update statement the updated field has no data.

I am using Microsoft access for Office 365 MSO (16.0.12624.203 48) 32 bit

My update statement is as follows

update  [Customer CL/SDM/TDM]  as b
inner join (
SELECT a.Customer, 
       a.[Technical Delivery Manger], 
       a.[Service Delivery Manager], 
       a.[Service Manager], 
       a.[Customer Lead], 
       a.[Organisation Owner], 
       IIf(IsNull([a].[Technical Delivery Manger]),
           IIf(IsNull([a].[Service Delivery Manager]),
               IIf(IsNull([a].[Service Manager]),
                   [a].[Organisation Owner],
                   [a].[Service Manager]),
               [a].[Service Delivery Manager]),
           [a].[Technical Delivery Manger]) AS expr1
FROM [Customer CL/SDM/TDM]  as a) as c 
on b.customer= c.customer
set [b].[contact owner]= c.expr1

[ADezii]

1. IMHO, when it comes to Nested IIf()s, I prefer to take a more direct and intuitive approach (at least to me).
2. Code Definition:
   

   Dim MYDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim varRet As Variant
   
   Set MYDB = CurrentDb
   Set rst = MYDB.OpenRecordset("Customer CL/SDM/TDM", dbOpenDynaset)
   
   With rst
     Do While Not .EOF
       If IsNull(![Technical Delivery Manager]) Then
         If IsNull(![Service Delivery Manager]) Then
           If IsNull(![Service Manager]) Then
             varRet = ![Organisation Owner]
           Else
              varRet = ![Service Manager]
           End If
         Else
           varRet = ![Service Delivery Manager]
         End If
       Else
         varRet = ![Technical Delivery Manager]
       End If
       .Edit
         ![Contact Owner] = varRet
       .Update
         .MoveNext
     Loop
   End With
   
   rst.Close
   Set rst = Nothing

[IMGNOTHUMB]https://bytes.com/attachments/attachment/10214d158714193 0/customer-clsdmtdm_before .jpg[/IMGNOTHUMB]
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10215d158714203 1/customer-clsdmtdm_after. jpg[/IMGNOTHUMB]

[twinnyfo]

I have grown averse to using update Queries at all. I have found it much easier and straight forward to use recordset and updating tables directly.

[NeoPa]

Personally, I would caution against too much reliance on VBA & Recordset processing. Certainly they have their place - but different approaches have different benefits.

SQL is generally best when updates should be considered atomic. It's possible to do using recordset, but certainly more involved - as you'd need to include Transaction commands within your code. It would also tend to perform less efficiently (Not that this would always be noticeable of course).

Horses for courses, and in this case I would suggest the approach taken by the OP (CBrits) is the more appropriate.

Don't let your comfort with certain aspects of Access be the only determining factor of what to use where ;-)

CBrits:
When a create a select statement this works but as soon as I turn it to an update statement the updated field has no data.

What do you mean by that? It may be helpful to post some illustrative data. It may be the values are being reset because the Field you're setting it to contains no data.

Certainly it seems that your query is unnecessarily complicated. What benefit do you expect to get from including the table twice? What benefit from putting one in a subquery? I see no obvious answer for either question.

Maybe try the following and see where you stand :

UPDATE [Customer CL/SDM/TDM]
SET    [Contact Owner]=Nz([Technical Delivery Manager]
                      ,Nz([Service Delivery Manager]
                      ,Nz([Service Manager]
                         ,[Organisation Owner])))