Syntax Error in UPDATE Statement

[McArthurGDM]

Hi,



I am running a SQL script through VBA with the runSQL command.

Basically, I am updating fields, 1 and a time, and checking to see if that field value is null... if it is, then update the field value.

However, I keep getting an error associated with the syntax of the SQL statement. Here is the statement, as it it is in VBA.

DoCmd.RunSQL ("UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT." & strQuery(i) & " = [ProjectsLink]." & strQuery(i) & " WHERE (((ProjectsT." & strQuery(i) & ") Is Null));")

strQuery(i), is the name of each field that I am updating. I can't figure out where... but somewhere in this is a syntax issue and I have no idea where.

Any help is appreciated.

Thanks,

[orange]

Instead of DoCmd.RunSQL

add a statement that says
Debug.print "UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT." & strQuery(i) & " = [ProjectsLink]." & strQuery(i) & " WHERE (((ProjectsT." & strQuery(i) & ") Is Null));"

Then look in the immediate window to see what Access understood.

[McArthurGDM]

Not sure what I am looking for exactly. It all looks the same.

[orange]

The debug.print will print the rendered Sql statement to the immediate window.
Try running your vba, then check the immediate window.

[McArthurGDM]

It's completely skipping "Inherent_Risk". Notice that the skip is blank and that it shows up AFTER Controls too.

NOT A CLUE WHY

strQuery(1) = "Legal_Analysis"
strQuery(2) = "CD_Applicability"
strQuery(3) = "CD_Applicability_Reason"
strQuery(4) = "Legal_Applicability"
strQuery(5) = "CD_Legal_Agreement"
strQuery(6) = "Stage_2_Status"
strQuery(7) = "Inherent_Risk"
strQuery(7) = "Controls"
strQuery(9) = "Residual_Risk"
strQuery(10) = "Analysis_Completion"
strQuery(11) = "Stage_3_Status"
strQuery(12) = "Target_Completion"
strQuery(13) = "Completion"

UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT.CD_Legal_Agreement = [ProjectsLink].[CD_Legal_Agreement] WHERE (((ProjectsT.CD_Legal_Agreement) Is Null));

UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT.Stage_2_Status = [ProjectsLink].[Stage_2_Status] WHERE (((ProjectsT.Stage_2_Status) Is Null));

UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT.Controls = [ProjectsLink].[Controls] WHERE (((ProjectsT.Controls) Is Null));

UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT. = [ProjectsLink].[] WHERE (((ProjectsT.) Is Null));

UPDATE ProjectsLink INNER JOIN ProjectsT ON ProjectsLink.ID2 = ProjectsT.ID2 SET ProjectsT.Residual_Risk = [ProjectsLink].[Residual_Risk] WHERE (((ProjectsT.Residual_Risk) Is Null));

[McArthurGDM]

This is going to sound dumb. But look at my count! I count strQuery as 7 - 7 in one part! So stupid! It works now.

Thanks for teaching me about the Immediate Window, that did prove useful when I looked at the code and noticed there was a step wrong in that particular portion!