Simple Function or Sub

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to writing functions and subs. I have been using access for 7 years
and have pretty much always found a solution for data analysis via queries,
but now I have a situation that I can't get a simple query to handle.

I need to call a function with 3 paramaters (vpart_id, vBeg_Date, vEnd_Date)
lets call it GetInvOuts that returns one numeric value.

From the function I need to get the sum of a field called QTY from a table
called INV_TRANS where the PART_ID = vpart_id and the TRANSACTION DATE >=
vBeg_Date and TRANSACTION_DATE <= vEnd_Date)

Any help is greatly appreciated.

This is what i have tried so far and I am getting no values in the query.

Public Function GetInvOuts(vPartID As String, vBeginDate As Date, vEndDate
As Date)

On Error Resume Next
Dim sqlStr As String

sqlStr = "SELECT SYSADM_INVENTORY_TRANS.PART_ID,
Sum(SYSADM_INVENTORY_TRANS.QTY) AS SumOfQTY" & _
" FROM SYSADM_INVENTORY_TRANS" & _
" WHERE (((SYSADM_INVENTORY_TRANS.TRANSACTION_DATE) Between
vBeginDate And vEndDate) AND ((SYSADM_INVENTORY_TRANS.TYPE)='O'))" & _
" GROUP BY SYSADM_INVENTORY_TRANS.PART_ID" & _
" HAVING (((SYSADM_INVENTORY_TRANS.PART_ID)=vPartID))"

DoCmd.RunSQL sqlStr
GetInvOuts = SumOfQty
End Function

Brent Fanguy
 
There are a few problems with the SQL string you are creating in the
function. Also, the RunSql method is only for Action queries (Append, Update,
Delete, MakeTable), so this approach will not work. The easy way to do this
is with a DSum function:

GetInvOuts = Nz(DSum("[Qty]", "INV_TRANS","[TRANSACTION_DATE] Between #" _
& vBeginDate & "# And #" & vEndDate & "# And [TYPE] = 'O' And " _
& "[PART_ID] = '" & vPartID & "'"),0)

This should(unless I have a syntax error :)) return the quantity based on
your criteria. I also wrapped the DSum with the Nz function. DSum will
return Null if no matches are found. That could cause an error if GetInvOuts
is not a variant data type. The Nz with the ,0 will return 0 instead of Null.
 
Back
Top