Update...set

[EMILYTAN via AccessMonster.com]

Why can't my set work?


mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty )" & _" SELECT '" &
strPartNumber & "', '" & strPositive & "' from WIPRawDetails" & _" WHERE
JobNumber= '" & Me.txtJobNumber & "' and PartNumber = '" & strPartNumber &
"'"
CurrentDb.Execute mySQL2, dbFailOnError


mySQL6 = "UPDATE WIPRawDetails SET W_QtyPending=W_QtyPending + '" &
strPositive & "' WHERE JobNumber= '" & Me.txtJobNumber & "' and PartNumber =
'" & strPartNumber & "'"
CurrentDb.Execute mySQL6, dbFailOnError

Thanks

 

[pietlinden]

Why can't my set work?

mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty )" & _" SELECT '" &
strPartNumber & "', '" & strPositive & "' from WIPRawDetails" & _" WHERE
JobNumber= '" & Me.txtJobNumber & "' and PartNumber = '" & strPartNumber &
"'"
CurrentDb.Execute mySQL2, dbFailOnError

mySQL6 = "UPDATE WIPRawDetails SET W_QtyPending=W_QtyPending + '" &
strPositive & "' WHERE JobNumber= '" & Me.txtJobNumber & "' and PartNumber =
'" & strPartNumber & "'"
CurrentDb.Execute mySQL6, dbFailOnError

Thanks

beats me. What happens if you use Debug.print mySQL6 to the
immediate window, copy it to a blank query in SQL view and try to run
it?

 

[EMILYTAN via AccessMonster.com]

Huh?
How?

Why can't my set work?

[quoted text clipped - 13 lines]
beats me. What happens if you use Debug.print mySQL6 to the
immediate window, copy it to a blank query in SQL view and try to run
it?

 

[John W. Vinson]

Why can't my set work?


mySQL2 = "INSERT INTO Kanban ( PartNumber, K_Qty )" & _" SELECT '" &
strPartNumber & "', '" & strPositive & "' from WIPRawDetails" & _" WHERE
JobNumber= '" & Me.txtJobNumber & "' and PartNumber = '" & strPartNumber &
"'"
CurrentDb.Execute mySQL2, dbFailOnError


mySQL6 = "UPDATE WIPRawDetails SET W_QtyPending=W_QtyPending + '" &
strPositive & "' WHERE JobNumber= '" & Me.txtJobNumber & "' and PartNumber =
'" & strPartNumber & "'"
CurrentDb.Execute mySQL6, dbFailOnError

Thanks

If this is literally what is in your code window, you have the line
continuation characters misplaced. Having a blank followed by an underscore
*at the end* of a line of code makes Access treat the next line as a
continuation of the line with the underscore. Having an underscore in the
middle of a line - as you do above - will simply give a syntax error.

You might also want to indicate what "doesn't work" about the query, and the
datatypes of the relevant fields.

John W. Vinson [MVP]

 

[EMILYTAN via AccessMonster.com]

I am sorry...
The syntax of the code is correct and I put it in a line.
But, just simply can't make it run for the update statement...

Why can't my set work?

[quoted text clipped - 10 lines]

Thanks

If this is literally what is in your code window, you have the line
continuation characters misplaced. Having a blank followed by an underscore
*at the end* of a line of code makes Access treat the next line as a
continuation of the line with the underscore. Having an underscore in the
middle of a line - as you do above - will simply give a syntax error.

You might also want to indicate what "doesn't work" about the query, and the
datatypes of the relevant fields.

John W. Vinson [MVP]

 

[EMILYTAN via AccessMonster.com]

Thanks....my sql can work d..thanks

I am sorry...
The syntax of the code is correct and I put it in a line.
But, just simply can't make it run for the update statement...
[quoted text clipped - 12 lines]

John W. Vinson [MVP]

 

[Guest]

Emily,

I'm wondering why you would format what should obviously be numeric values
(K_Qty, and W_QtyPending) as strings rather than as numbers? I can see where
your JobNumber and PartNumber fields might be alpha-numeric, but cannot
imagine why you would make a quantity field a string value.

Or could that be what is causing your problem. If these fields are actually
numeric, then you should not wrap the values you are trying to write to your
table in single quotes as you have.

HTH
Dale