Sql case problem

[AlvaroCity]

Hi there.

I bumped into a problem while programming.
I firstly have to say that I'm not a professional programmer so I have been doing all this on the go.

I have a DB for a company. I create dispatches notes to pressing a button and it also updates the stock.

Sometimes, for unforeseen reasons, the number of units displays on the dispatch note is higher than the actual stock. That's not a problem as I know for sure that I have the necessary stock in the company.
The problem comes up when the programme updates the stock. When the units displays on the dispatch note is higher than the stock, the SQL does not update properly and leaves the stock as it is instead of leaving the stock at zero.
Example:
I have 2 units on stock and I create a dispatch note for 4 units. When updating the stock instead of 0 units for stock, the program keeps the 2 units.

I think what I need is to use "CASE" or "IF" but I dont know how. I hope you guys can help me with this.

         Ssql = "UPDATE (tblPiezas INNER JOIN tblPedidoDetalle ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]) INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID]" & _
                "SET tblPiezas.Stock= [Stock]-[NumeroPiezas]" & _
                "WHERE (((tblpedidodetallealbaran.AlbaranID)=" & StrAlbaran & "));"

 

[Uncle Gizmo]

Don't look like you have enough spaces:-

Ssql = "UPDATE (tblPiezas INNER JOIN tblPedidoDetalle ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]) INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID] " & _
                "SET tblPiezas.Stock= [Stock]-[NumeroPiezas] " & _
                "WHERE (((tblpedidodetallealbaran.AlbaranID)=" & StrAlbaran & "));"

I added a Space Here:-
Before:- .[PedidoDetalleID]" & _
After:--- .[PedidoDetalleID] " & _

and Here:-
Before:- .[Stock]-[NumeroPiezas]" & _
After:--- .[Stock]-[NumeroPiezas] " & _

 

[AlvaroCity]

Thank you for that.
The thing is that the UPDATE query works but it does as long as the "numero de piezas" (Number of parts) is lower than the Stock.

I don't know if you could give me a hand with an If or Case formula. Like if the Stock is lower than number of parts..... this else " my current query".

many thanks

 

[The_Doc_Man]

There is such a thing as an IIF, but if all you wanted to do was not have negative numbers, then run TWO queries - one that does the subtraction without question and one that sets the fields to zero if they are negative. Then, to prevent interference or accidental intrusion, you can run those two queries as a transaction.

If you are not familiar with this, look up "Access Transactions" which will tell you about running code that includes a "BEGIN TRANSACTION" item, the multiple SQL queries, and a "COMMIT" item. Everything between the BEGIN and COMMIT options will be treated as a single (complex) transaction. Then you don't worry about a complex IIF (which slows things down and is harder to code when you are not used to it.)

https://msdn.microsoft.com/en-us/library/bb243806(v=office.12).aspx = DAO transaction discussion and example

https://msdn.microsoft.com/en-us/library/bb243806(v=office.12).aspx = another example and discussion on transactions

 

[arnelgp]

Ssql = "UPDATE (tblPiezas INNER JOIN tblPedidoDetalle ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]) INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID] " & _
"SET tblPiezas.Stock= IIF([Stock]<[NumeroPiezas], 0, [Stock]-[NumeroPiezas])" & _
"WHERE (((tblpedidodetallealbaran.AlbaranID)=" & StrAlbaran & "));"

 

[AlvaroCity]

Thank you so much!!! That was exactly what I needed it.
I works now perfectly.
Thank you everyone too. You're the best.

 

[arnelgp]

ur welcome pal.