DLookup not returning correct value

[dccjr3927]

I am trying to check qty on an item before logging a work order. The following line is still not quite right.

If DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & Me.cbxSKU & "'") >= Me.tbxSKUQty Then
.
.
Else
MsgBox "There is insufficient item inventory to fulfill this order. Please notify administrator.", vbOKOnly
End If

It is like there is no inventory for that item. (Table shows 9 in stock, Me.tbxSKUQty = 1). I have double checked all of my spelling and names.

 

[pbaldy]

What value does the DLookup() return? Have you set a breakpoint and made sure the correct sku was in the criteria?

 

[theDBguy]

Also, it seems like you're storing a calculated value (SaleableQty) in your table. Is this correct?

 

[dccjr3927]

The cbxSKu is returning: CE-WATCH-MCLIP-BLK
The SaleableQty is returning: 9

Which is all correct. Oddly, though, there is a space in front of the 9.

 

[pbaldy]

I've had situations where I had to use a function like CInt() or CLng() to make sure one or both values was seen as numeric.

 

[dccjr3927]

Where would I use them in my code? I have never used either of those. What is the syntax.

 

[pbaldy]

Around each:

If CInt(DLookup("SaleableQty", "Inventory", "Inventory.SKU = '" & Me.cbxSKU & "'")) >= CInt(Me.tbxSKUQty) Then

I'd probably also use the Nz() function to avoid the error you'll get if either value is Null.

 

[The_Doc_Man]

Normally, DLookup will return a variant, but there are cases where the variant is forced to a particular type. What is the data type of [SaleableQty]? This could be a simple type mismatch if, for example, that quantity is stored as a text string due to the way it was defined in the table. Having a leading space suggests that it thinks you are looking at a text item, which is why you would need a VAL() function or a CLNG() function.

This all would be different if you used the more traditional method of tracking inventory, one that is based on "sum of transactions." This forum is loaded with tons (figuratively speaking, of course) of articles on "Inventory" so you could find a lot on the subject with the SEARCH function.