By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

calculation question

100+
P: 166
I have an unbound text box which I use to get a calculation, called DM_SampleWt. This text box's control source is:
=GetSize([cbo_matTypeID],Nz([matBatchweight],0),[Form].[Parent]![txtpan],[Form].[Parent]![cbo_panID].[column](2))
I have written a function that will get the required sample weight of a material selected from my form
[cbo_matTypeID]- represents a combo box that allows the user to select the material type (bound)
matBatchweight- represents the batchweight of each material (user input, bound)
[txtpan]-bound text box representing the number of pans that need to be made
cbo_panID.column(2)- bound combo box, represents the pansize needed for the job, the column 2 represents the formula used in the calculation needed.

Here is my function:
Public Function GetSize(matType As Integer, BatchWeight As Double, NoOfPans As Double, PanFormulaValue As Double) As Double
'returns the required sample weight, 'DM_ReqSampWt' calculation

Select Case matType
'Cement, Coarse, Fine
Case 1, 2, 3
GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27
Case 4
'Pigment
GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27 / 0.0022
Case Else
GetSize = 0
End Select
End Function

DM_SampleWeight is automatically calculated after all of the above are input. The problem I am having (the function works perfectly) is that for material cement (cbo_matTYpeID = 1), I need to sum the DM_SampleWeight and I can't because I can not get a TOTAL SUM for a calculated value. I tried to use the same function like this:

=GetSize(Sum([cbo_matTypeID]=1),Nz([matBatchweight],0),[Form].[Parent]![txtpan],[Form].[Parent]![cbo_panID].[column](2))

How can I approach this problem? Thank you
Dec 9 '08 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,638
@csolomon
You could try building the Summation Logic into the Function like so, but I have no idea if this will actually work:
Expand|Select|Wrap|Line Numbers
  1. Public Function GetSize(matType As Integer, BatchWeight As Double, NoOfPans As Double, _
  2.                         PanFormulaValue As Double) As Double
  3. 'returns the required sample weight, 'DM_ReqSampWt' calculation
  4. Select Case matType
  5.   'Cement, Coarse, Fine
  6.   Case 1    'Unique case?
  7.     Dim MyDB As DAO.Database
  8.     Dim rstCalc As DAO.Recordset
  9.     Dim dblRunningSum As Double
  10.  
  11.     Set MyDB = CurrentDb()
  12.     'Could also use an SQL Statement as basis for Recordeset ([Type] = 1)
  13.     Set rstCalc = MyDB.openrecordset("tblCalculation", dbOpenForwardOnly)
  14.  
  15.     With rstCalc
  16.       Do While Not rstCalc.EOF
  17.         If ![Type] = 1 Then
  18.           dblRunningSum = dblRunningSum + (![BatchWeight] * ![NumOfPans] _
  19.                           * ![PanFormulaValue] / 27)
  20.         End If
  21.           .MoveNext
  22.       Loop
  23.       GetSize = dblRunningSum    'Set the Function = the Aggregate Total
  24.     End With
  25.     rstCalc.Close
  26.     Set rstCalc = Nothing
  27.   Case 2, 3
  28.     GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27
  29.   Case 4
  30.     'Pigment
  31.     GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27 / 0.0022
  32.   Case Else
  33.     GetSize = 0
  34. End Select
  35. End Function
Dec 10 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.