Query to Subtract Inventory if a certain number?!?!

[jasonstewart]

I have a query that looks like this.

UPDATE CWLine INNER JOIN OrderDetails ON [CWLine].[Barcode]=[OrderDetails].[Barcode] SET CWLine.Qty = [CWLine].[Qty]-[OrderDetails].[Qty]
WHERE [OrderDetails].[OrderNo] Between [Enter Starting Order Number] And 999999;

This is what I need to do. I have a salesmans jewelry line come in from the field where he is entering orders into a handheld then sending that information back to an 'Access' Database on a server. He sells 2 types of orders. Order or Delivery, if it is a 'Delivery' then when the handheld syncs with my database I have to subtract the Inventory from that particular salesmans line, in the Table [CWLine] in the same database. The only way that the [OrderDetails] table knows that it is a delivery is that the order number is a (6) digit number instead of a (7) digit number. Any ideas on why this doesn't work. It kinda works, I think that it is just subtracting the first one with the [OrderNo] and skipping the rest and that is no good because some deliverys can be 40+ line items. Any help would be greatly appreciated and if you need further explination I would be happy to give you what you need. Thanx

 

[jrbarnett]

Hi

Are the order numbers stored as text fields in the database? If so you can use
Len(Fieldname) to return the field name length in characters
so this can be used to check if the length is 7.

I don't fully understand your problem from the above description, but try the following modification:

UPDATE CWLine INNER JOIN OrderDetails ON [CWLine].[Barcode]=[OrderDetails].[Barcode] SET CWLine.Qty = [CWLine].[Qty]-[OrderDetails].[Qty]
WHERE Len ([OrderDetails].[OrderNo]) = 7 And [OrderDetails].[OrderNo] Between [Enter Starting Order Number] And 999999;

John