Data Type Mismatch... HELP!!!

[josie]

Can't see why I am getting a Error 3464: Data type mismatch in critical expression on the following code;

Private Sub Quantity_AfterUpdate()
Dim intStockCurrent As Integer
Dim intStockSold As Integer
Dim intStockNew As Integer


intStockCurrent = Nz(DLookup("[StockOnHand]", "tblStock", "[SerialNo] = '" & Me!SerialNo & "'"), "0")
intStockSold = Me!Quantity

'check value
MsgBox intStockCurrent, , "CurrentStock"
'check value
MsgBox intStockSold, , "StockSold"

intStockNew = intStockCurrent - intStockSold
'check value
MsgBox intStockNew, , "New Stock Level"

strSQL = "Update tblStock SET StockOnHand = " & intStockNew & " WHERE tblStock.SerialNo = " & Me!SerialNo & ";"

'check sql string
MsgBox (strSQL)
DoCmd.RunSQL (strSQL)


End Sub


I am trying to update the value of a field StockOnHand from the tblStock. This field has a data type of integer. Where I am going wrong??

thanks in advance,
josie

 

[pbaldy]

The DLookup treats SerialNo as a text value, while the UPDATE query treats it as a numeric value. Whichever is wrong is probably your problem (surround text with single quotes, numeric not).

 

[boblarson]

Also, I think you have this problem:

intStockCurrent = Nz(DLookup("[StockOnHand]", "tblStock", "[SerialNo] = '" & Me!SerialNo & "'"), "0")

Get rid of the quotes around the zero.

intStockCurrent = Nz(DLookup("[StockOnHand]", "tblStock", "[SerialNo] = '" & Me!SerialNo & "'"), 0)

 

[josie]

Okay, so now I'm confused...
the dlookup isn't looking up the value of the serial no it is only using it for a comparison to lookup the current stock level.
and nor the update - again it is only using it as a comparison.
if I comment out the sql things work fine as far as I get the right results back in the msgboxes so that leads me to believe that there is something wrong with the sql.
In the table serialNo data type is text.
Am I totally missing something here??
josie

 

[josie]

Hmm....
removed the in the DLookup - still same problem.
I don't get it the message box for new stock level is returning the right value. Now all I want to do is put that new value in replace of the old value as stockOnHand in the stock table.
Things should be so simple :~
josie

 

[pbaldy]

strSQL = "Update tblStock SET StockOnHand = " & intStockNew & " WHERE tblStock.SerialNo = '" & Me!SerialNo & "';"

 

[josie]

Okay, I think I get what you were saying with the quotes thing Paul.
I have given it a bit of a run - will have to do some further testing.
Thanks!!
my many appreciatives
josie

 

[DCrake]

Simple Software Solutions

In your line

strSQL = "Update tblStock SET StockOnHand = " & intStockNew & " WHERE tblStock.SerialNo = " & Me!SerialNo & ";"

Your syntax is slightly out as you need to insert a space after the intStockNew value as the resulting code would error, such as

Update tblStock SET StockOnHand = 21WHERE tblStock.SerialNo = 123;

strSQL = "Update tblStock SET StockOnHand = " & intStockNew & " " & WHERE tblStock.SerialNo = " & Me!SerialNo & ";"

CodeMaster:

 

[josie]

Thanks to all for replies!
Another problem sovled and another feature ticked off.
What can I say.... u guys r great!
josie

 

[pbaldy]

In your line

strSQL = "Update tblStock SET StockOnHand = " & intStockNew & " WHERE tblStock.SerialNo = " & Me!SerialNo & ";"

Your syntax is slightly out as you need to insert a space after the intStockNew value as the resulting code would error, such as

There is a space before the word WHERE; that code will work fine as is.

Josie, glad we got it sorted out for you.