How come you can't use NZ with update query?

[SIMMS7400]

Hi Folks -



I have an update query that I need to modify. There are situations where the CCTEMP.Short_Code is not unique and order to make it unique, I need to bring in another column.

This is my current code:

    strStep = "Step 5 : Update Cost Center Info"
    strSQL = "" & _
            "UPDATE [Global_Monthly_Headcount]" & _
            "INNER JOIN [CCTEMP]" & _
            "ON [Global_Monthly_Headcount].[Cost Center - Short Name] = [CCTEMP].[Short_Code]" & _
                "SET" & _
                    "[Global_Monthly_Headcount].[Cost Center - Long Name] = Nz([CCTEMP].[Long_Code], Null)," & _
                    "[Global_Monthly_Headcount].[Cost Center - Division] = Nz([CCTEMP].[Division], Null)," & _
                    "[Global_Monthly_Headcount].[Cost Center - Function] = Nz([CCTEMP].[Function], Null)," & _
                    "[Global_Monthly_Headcount].[Cost Center - Subfunction] = Nz([CCTEMP].[Subfunction], Null);"
    db.Execute strSQL, dbFailOnError

But I want to use this but it just runs and runs and runs and doesn't complete. How would I go about achieving this?

    strStep = "Step 5 : Update Cost Center Info"
    strSQL = "" & _
            "UPDATE [Global_Monthly_Headcount]" & _
            "INNER JOIN [CCTEMP]" & _
            "ON [Global_Monthly_Headcount].[Cost Center - Short Name]+Nz([Global_Monthly_Headcount].[Cost Center - Division],""foo"") = [CCTEMP].[Short_Code]+Nz([CCTEMP].[Division],""foo"")" & _
                "SET" & _
                    "[Global_Monthly_Headcount].[Cost Center - Long Name] = Nz([CCTEMP].[Long_Code], Null)," & _
                    "[Global_Monthly_Headcount].[Cost Center - Division] = Nz([CCTEMP].[Division], Null)," & _
                    "[Global_Monthly_Headcount].[Cost Center - Function] = Nz([CCTEMP].[Function], Null)," & _
                    "[Global_Monthly_Headcount].[Cost Center - Subfunction] = Nz([CCTEMP].[Subfunction], Null);"
    db.Execute strSQL, dbFailOnError

[isladogs]

The Nz function is used to create an alternate value when a field value is null. It makes no sense to set that alternate value to be Null even if it were possible.

[Minty]

I'm not sure you can use a + in a join expression in Access.
You could get away with it in T-SQL .

Have you debug.printed your strSQL as I don't think that would work ? ""Foo"" should be in single quotes 'Foo'

Unless that's just you anonymising your code a bit?

[ranman256]

try not to use code sql, instead create queries. They always get the syntax right.

[SIMMS7400]

Thanks all! So is there no way to solve my issue?

[CarlettoFed]

The second SQL statement is wrong, you cannot relate more tables by writing in that way. You should post a sample file to be sure we can help you.