medihub_guy
Member
- Local time
- Today, 11:38
- Joined
- Aug 12, 2023
- Messages
- 66
Good day,
I'm trying to calculate quantity on hand (QOH) for a drug that has 2 batch numbers. The query constructed in the Access query builder shows the correct information. However, when constructing the SQL statement for VBA, it shows the wrong values. Here is the query in SQL view from the access query builder:
SELECT [GenericNameStrength_childdrug] & " " & [Abbreviation_form] AS DrugNameStrengthForm, ChildDrug_T.GenericNameStrength_childdrug, Form_T.Abbreviation_form, ChildDrug_T.BatchNumber_childdrug, ChildDrug_T.ExpirationDate_childdrug, Nz([SumOfQuantity_goodsin])-Nz([SumOfQuantity_goodsout]) AS QOH, StockIn_Q.LocationID, ChildDrug_T.ParentDrugID, ChildDrug_T.PackageSize_childdrug, ChildDrug_T.UnitCost_childdrug, ChildDrug_T.Active_childdrug, Supplier_T.CompanyName_supplier, ChildDrug_T.InvoiceNumber_goodsin, ChildDrug_T.Date_goodsin, Manufacturer_T.CompanyName_manufacturer, OrderType_T.OrderType_description, ChildDrug_T.ChildDrugID, ChildDrug_T.OrderTypeID
FROM (((Form_T INNER JOIN (((StockOut_Q RIGHT JOIN ChildDrug_T ON StockOut_Q.ChildDrugID = ChildDrug_T.ChildDrugID) LEFT JOIN StockIn_Q ON ChildDrug_T.ChildDrugID = StockIn_Q.ChildDrugID) INNER JOIN ParentDrug_T ON ChildDrug_T.ParentDrugID = ParentDrug_T.ParentDrugID) ON Form_T.FormID = ParentDrug_T.FormID) INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID) INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID) INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID
WHERE (((StockIn_Q.LocationID)=[Forms]![3-General_SplashScreen_F]![LocationID]) AND ((ChildDrug_T.ParentDrugID)=[forms]![Pharmacy_ParentDrugList_ChildDrug_Edit_F]![ParentDrugID]));
I understand that Nz is not a function recognized by SQL. So, I wrote this in my VBA editor:
Function ChildDrug_Edit_Q()
Dim db As Database
Dim qd As QueryDef
Dim qr As String
On Error Resume Next
Set db = CurrentDb
On Error GoTo 0
CurrentDb.QueryDefs("PassThrough_Q").SQL = "SELECT *, " _
& "(Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form)) As DrugNameStrengthForm, " _
& "StockIn_Q.LocationID, PriceTable_T.MarkUp_pricetable, PriceTable_T.TaxRate_pricetable, " _
& "PriceTable_T.DispensingFee_pricetable, Supplier_T.CompanyName_supplier, OrderType_T.OrderType_description, StockIn_Q.[SumOfQuantity_goodsin], StockOut_Q.[SumOfQuantity_goodsout], " _
& "(IsNull([SumOfQuantity_goodsin],0)- IsNull([SumOfQuantity_goodsout],0)) AS QOH " _
& "FROM ChildDrug_T " _
& "INNER JOIN (SELECT GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID, Sum(GoodsOut_T.Quantity_goodsout) AS SumOfQuantity_goodsout FROM GoodsOut_T WHERE GoodsOut_T.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & " GROUP BY GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID) AS StockOut_Q ON StockOut_Q.ChildDrugID = ChildDrug_T.ChildDrugID " _
& "INNER JOIN (SELECT GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID, Sum(GoodsIn_T.Quantity_goodsin) AS SumOfQuantity_goodsin FROM GoodsIn_T WHERE GoodsIn_T.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & " GROUP BY GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID) AS StockIn_Q ON StockIn_Q.ChildDrugID = ChildDrug_T.ChildDrugID " & "INNER JOIN ParentDrug_T ON ParentDrug_T.ParentDrugID = ChildDrug_T.ParentDrugID " _
& "INNER JOIN Form_T ON ParentDrug_T.FormID = Form_T.FormID " _
& "INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID " _
& "INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID " _
& "INNER JOIN PriceTable_T ON PriceTable_T.PriceTableID = ChildDrug_T.PriceTableID " _
& "INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID " _
& "WHERE Stockin_Q.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & "" _
& "AND ChildDrug_T.Active_childdrug = '-1' " _
& "AND ChildDrug_T.ParentDrugID =" & Forms!Pharmacy_ParentDrugList_ChildDrug_Edit_F!Subform!ParentDrugID & "" _
& "ORDER BY ChildDrug_T.ExpirationDate_childdrug"
End Function
Any advice?
Stephen
I'm trying to calculate quantity on hand (QOH) for a drug that has 2 batch numbers. The query constructed in the Access query builder shows the correct information. However, when constructing the SQL statement for VBA, it shows the wrong values. Here is the query in SQL view from the access query builder:
SELECT [GenericNameStrength_childdrug] & " " & [Abbreviation_form] AS DrugNameStrengthForm, ChildDrug_T.GenericNameStrength_childdrug, Form_T.Abbreviation_form, ChildDrug_T.BatchNumber_childdrug, ChildDrug_T.ExpirationDate_childdrug, Nz([SumOfQuantity_goodsin])-Nz([SumOfQuantity_goodsout]) AS QOH, StockIn_Q.LocationID, ChildDrug_T.ParentDrugID, ChildDrug_T.PackageSize_childdrug, ChildDrug_T.UnitCost_childdrug, ChildDrug_T.Active_childdrug, Supplier_T.CompanyName_supplier, ChildDrug_T.InvoiceNumber_goodsin, ChildDrug_T.Date_goodsin, Manufacturer_T.CompanyName_manufacturer, OrderType_T.OrderType_description, ChildDrug_T.ChildDrugID, ChildDrug_T.OrderTypeID
FROM (((Form_T INNER JOIN (((StockOut_Q RIGHT JOIN ChildDrug_T ON StockOut_Q.ChildDrugID = ChildDrug_T.ChildDrugID) LEFT JOIN StockIn_Q ON ChildDrug_T.ChildDrugID = StockIn_Q.ChildDrugID) INNER JOIN ParentDrug_T ON ChildDrug_T.ParentDrugID = ParentDrug_T.ParentDrugID) ON Form_T.FormID = ParentDrug_T.FormID) INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID) INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID) INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID
WHERE (((StockIn_Q.LocationID)=[Forms]![3-General_SplashScreen_F]![LocationID]) AND ((ChildDrug_T.ParentDrugID)=[forms]![Pharmacy_ParentDrugList_ChildDrug_Edit_F]![ParentDrugID]));
I understand that Nz is not a function recognized by SQL. So, I wrote this in my VBA editor:
Function ChildDrug_Edit_Q()
Dim db As Database
Dim qd As QueryDef
Dim qr As String
On Error Resume Next
Set db = CurrentDb
On Error GoTo 0
CurrentDb.QueryDefs("PassThrough_Q").SQL = "SELECT *, " _
& "(Concat(ChildDrug_T.GenericNameStrength_childdrug, ' ', Form_T.Abbreviation_form)) As DrugNameStrengthForm, " _
& "StockIn_Q.LocationID, PriceTable_T.MarkUp_pricetable, PriceTable_T.TaxRate_pricetable, " _
& "PriceTable_T.DispensingFee_pricetable, Supplier_T.CompanyName_supplier, OrderType_T.OrderType_description, StockIn_Q.[SumOfQuantity_goodsin], StockOut_Q.[SumOfQuantity_goodsout], " _
& "(IsNull([SumOfQuantity_goodsin],0)- IsNull([SumOfQuantity_goodsout],0)) AS QOH " _
& "FROM ChildDrug_T " _
& "INNER JOIN (SELECT GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID, Sum(GoodsOut_T.Quantity_goodsout) AS SumOfQuantity_goodsout FROM GoodsOut_T WHERE GoodsOut_T.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & " GROUP BY GoodsOut_T.LocationID, GoodsOut_T.ChildDrugID) AS StockOut_Q ON StockOut_Q.ChildDrugID = ChildDrug_T.ChildDrugID " _
& "INNER JOIN (SELECT GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID, Sum(GoodsIn_T.Quantity_goodsin) AS SumOfQuantity_goodsin FROM GoodsIn_T WHERE GoodsIn_T.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & " GROUP BY GoodsIn_T.LocationID, GoodsIn_T.ChildDrugID) AS StockIn_Q ON StockIn_Q.ChildDrugID = ChildDrug_T.ChildDrugID " & "INNER JOIN ParentDrug_T ON ParentDrug_T.ParentDrugID = ChildDrug_T.ParentDrugID " _
& "INNER JOIN Form_T ON ParentDrug_T.FormID = Form_T.FormID " _
& "INNER JOIN Manufacturer_T ON ChildDrug_T.ManufacturerID = Manufacturer_T.ManufacturerID " _
& "INNER JOIN Supplier_T ON ChildDrug_T.SupplierID = Supplier_T.SupplierID " _
& "INNER JOIN PriceTable_T ON PriceTable_T.PriceTableID = ChildDrug_T.PriceTableID " _
& "INNER JOIN OrderType_T ON ChildDrug_T.OrderTypeID = OrderType_T.OrderTypeID " _
& "WHERE Stockin_Q.LocationID =" & Forms![3-General_SplashScreen_F]!LocationID & "" _
& "AND ChildDrug_T.Active_childdrug = '-1' " _
& "AND ChildDrug_T.ParentDrugID =" & Forms!Pharmacy_ParentDrugList_ChildDrug_Edit_F!Subform!ParentDrugID & "" _
& "ORDER BY ChildDrug_T.ExpirationDate_childdrug"
End Function
Any advice?
Stephen