Order Query

[Hardhit]

Hello All,

I have a table which contains the orders from a customer. This table is
filled out of an excel sheet which I import.
The datastructure is as follows :

PartNr OrderQty DelivDate PendQty
12345 100 BackOrd 200
12345 200 10/01/08
12345 150 20/01/08

Only on the first line for each partnumber there could be a PendQty.
The pending Qty shows the quantity received but not yet confirmed by the
customer.
When I want to calculate the open quantity to deliver I do a calculation in
a query where I deduct from the OrderQty the PendQty.
This means in the above shown example that the OrderQtyCalc results in -100.
This is fine but now my problem appears.
I would now need to deduct this quantity from the next OrderQty. How would I
do this in access ?

When this is done I attach this to a Bill of Material system to calculate
the parts to order and to send this information to the suppliers.
The result of the query should be like the example below.

PartNr OrderQty DelivDate PendQty OrderQtyCalc
12345 100 BackOrd 200 -100
12345 200 10/01/08 100
12345 150 20/01/08 150

Thanks in advance for the help

Peter

 

[Ken Sheridan]

As the DelivDate column must be of text data type rather than date/time to
cater for the "BackOrd" value in the first row then you can't use a simple
subquery correlated on the dates. You'll need to handle it in code by
wrapping everything up in a function. The following should do it:

Function GetOrderQtyCalc(lngPartNr As Long, _
intOrderQty As Integer, _
strDelivDate As String, _
varPendQty As Variant)

Dim strCriteria As String
Dim dtmPrecDate As Date

If strDelivDate = "BackOrd" Then
GetOrderQtyCalc = intOrderQty - varPendQty
Else
strCriteria = "PartNr = " & lngPartNr & _
" And CDate(IIF(IsDate(DelivDate),DelivDate,0)) < #" & _
Format(CDate(strDelivDate), "mm/dd/yyyy") & "#"
dtmPrecDate = DMax("CDate(IIF(IsDate(DelivDate),DelivDate,0))", _
"YourOrderTable", strCriteria)
If dtmPrecDate = 0 Then
strCriteria = "PartNr = " & lngPartNr & _
" And DelivDate = ""BackOrd"""
GetOrderQtyCalc = intOrderQty + DLookup("OrderQty - PendQty", _
"YourOrderTable", strCriteria)
Else
GetOrderQtyCalc = intOrderQty
End If
End If

End Function

Pate this into a standard module and call it in a query like so:

SELECT PartNr, OrderQty, DelivDate, PendQty,
GetOrderQtyCalc(PartNr,OrderQty,DelivDate,PendQty)
AS OrderQtyCalc
FROM YourOrderTable
ORDER BY PartNr,IIf(IsDate(DelivDate),DelivDate,0);

The above does assume that the PendQty form the first row per PartNr is only
carried forward one row, so even if it resulted in a negative OrderQtyCalc in
that row it would not be carried forward to a subsequent row.

Ken Sheridan
Stafford, England

 

[Hardhit]

Hi Ken,

I tried this code and I keep getting errors. I have replaced in the code the
"YourOrderTable" bij the table involved but it's not working.

I get the Error Datatype mismatch in criteria expression.

The partnumber in the table is not numeric but Txt. I tried changing
lngPartNr as Long to strPartNr as string. I then also changed the variable
in the function so that it is the same as the called variable.

Regards,

 

[Hardhit]

Thanks Ken,

I fixed up the code as you suggested and I don't get the errors anymore.

Regards,
Peter