Cannot Find My Syntax Error

[BrianFawcett]

I am trying to write this UPDATE code below and I am getting a syntax and cannot for the life of me find it. Can someone with a fresh pair of eyes help me out.

strUpdateADMINRecord = "UPDATE [Data Table]" & _
"SET [AAM Approved] = '" & ![AAM Approval] & "', [AAM Date Approved] = '" & ![AAM Date Approved] & "', [AAM Time Approved] = '" & ![AAM Time Approved] & _
"', [Assigned To] = '" & ![AssignedTo] & "', [Dealer] = '" & ![Channel Number] & "', [Dealer Name] = '" & ![Channel Name] & _
"', [Dealer Type] = '" & ![Channel Type] & "', [Order Type] = '" & ![Order Type] & _
"', [PO or Customer] = '" & ![PO or Customer] & _
"', [Product Group 1] = '" & ![Product Group 1] & "', [Product Group 2] = '" & ![Product Group 2] & _
"', [Activated Status] = '" & ![Activated Status] & "', [Date Activated] = '" & ![Date Activated] & "', [Time Activated] = '" & ![Time Activated] & _
"', [Afterclose Status] = '" & ![Afterclose Status] & "', [Approval Status] = '" & ![Approval Status] & _
"', [Date Approved] = '" & ![Date Approved] & "', [Time Approved] = '" & ![Time Approved] & _
"', [Entered Unreleased] = '" & ![Entered Unreleased] & "', [Date Entered Unreleased] = '" & ![Date Entered Unreleased] & "', [Time Entered Unreleased] = '" & ![Time Entered Unreleased] & _
"', [QA Time Approved] = '" & ![QA Time Approved] & _
"', [Oracle Number] = '" & ![Sales Order] & _
"', [Date Entered] = '" & ![Date Entered] & _
"', [Time Entered] = '" & ![Time Entered] & "' " & _
"WHERE [Data Table].[InHouse Ref] Like '" & ![InHouse Ref] & "' ";

 

[spikepl]

insert debug.print strUpdateADMINRecord into your code and inspect the result in the immediate window. If that dosn't help paste that result here.

 

[Brianwarnock]

Are the "*" missing in the Like statement.

Notice that you have [assigned to] and [assignedto] is that correct.

Brian

 

[spikepl]

Many concoct long SQL statements, run sour in single/double quotes, get a syntax error, and then come here. All because that is not the way to make lengthy SQL-statements.

Do things 1 step at a time: build the SQL bit by bit, check that that works, and then add one more bit. Use the debug.print to see what the string looks like. Use the query builder: paste the SQL into the SQL-view, and there it will complain about syntax errors. Conversely, build the SQL in the query builder, because then you know it works, paste it into your code and then modify as required. Bit by bit.

 

[vbaInet]

On quick glance I don't see anything particularly wrong with it, but I will mention two things:

1. Wrap your values appropriately based on the data type. E.g. if [Date Approved] is a Date/Time field then you need to wrap it in hash (#) characters.
2. This line:

"WHERE [Data Table].[InHouse Ref] Like '" & ![InHouse Ref] & "' ";

Should be:

"WHERE [Data Table].[InHouse Ref] [COLOR=Red]=[/COLOR] '" & ![InHouse Ref] & "[COLOR=Red]';[/COLOR]"

Note the red bits. You are not using any wild characters so LIKE is not needed.

Edit: A very slow reply indeed

 

[smig]

one obvious problem I see is you add no spaces:

strUpdateADMINRecord = "UPDATE [Data Table]" & _
"SET [AAM Approved] ...

will result in:

"UPDATE [COLOR=darkred][Data Table]SET[/COLOR] [AAM Approved] ...

For such a long QueryString won't it be easier to create it in the query grid, and run the query ?