Dsum update querie

[Guest]

HI

I want to track inventory delivered by a producer to where it is sold.

The first part is to allocate shipments to delivery note reccords, and I
mannaged to create this query. Very proud of myself.

SELECT [Delivery detail].DeliverydetailNo, [Delivery detail].CasesDelivered,
DSum("[CasesShipped]","Shipment Detail","[Shipment
Detail]![DeliveryDetialID]=" & [Delivery detail]![DeliverydetailNo]) AS
Shipped, [CasesDelivered]-[Shipped] AS Expr1
FROM [Delivery detail];

Now how do I get the Dsum formula to wright a zero in Shipped if it can not
find Shipment Detail]![DeliveryDetialID]=" & [Delivery
detail]![DeliverydetailNo]).

Then I also just want to show the records where [CasesDelivered]-[Shipped]
AS Expr1 is >0.

This is fun I just get stuck sometimes.

 

[Michel Walsh]

You cannot use the aliases in the WHERE clause, so, you have to repeat the
whole expression:

SELECT [Delivery detail].DeliverydetailNo, [Delivery detail].CasesDelivered,
DSum("[CasesShipped]","Shipment Detail","[Shipment
Detail]![DeliveryDetialID]=" & [Delivery detail]![DeliverydetailNo]) AS
Shipped, [CasesDelivered]-[Shipped] AS Expr1
FROM [Delivery detail]
WHERE CasesDelivered-DSum("[CasesShipped]","Shipment Detail",
"[DeliveryDetialID]=" & [Delivery detail].[DeliverydetailNo]) > 0




Hoping it may help,
Vanderghast, Access MVP