Results 1 to 7 of 7

VBA - Minimum, Maximum and Average based in Search

  1. #1
    acm007 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    12

    Question VBA - Minimum, Maximum and Average based in Search

    Hi,

    Please see picture below:

    I would like to create a 6 box fields to display:
    1) Minimum Value
    2) Maximum Value
    3) Average Value

    Of two columns (pH and Visc.).

    However, the complicated part is that it needs to be based on the search performed. Basically, every time a new search is done, the numbers should change accordingly.


    Here is my current form and code:
    Click image for larger version. 

Name:	004b.jpg 
Views:	17 
Size:	126.8 KB 
ID:	35021

    Any help will be much appreciated!!!

    Code:
    'example custom report filter form'Private Sub cboCustomer_Click()
    '   MsgBox Me.cboCustomer
    'End Sub
    
    
    'If Not IsNull(Me."Search Box Text Box Name") Then
    '   Str"Control Source of Text Field" = "["Control Source of Text Field"] = '" & Me."Search Box Text Box Name" & "'"
    '   strSQL = strSQL & " AND " & str"Control Source of Text Field"
    'End If
    
    
    
    
    Private Sub CmdSearch_Click()
          Dim strSQL As String
          Dim strBatchID, StrProductCategory, strDateofManufacture, strPurchaseOrder As String
          Dim strFormulatorLot, strCustomerName, strHasNote, strProductName, strLedgerNumber, strProductCode, strJulianDate As String
          Dim strBatchSize, strBatchYield, strMainTank, strFragrance, strColor, strCOA, strInitialSpecificGravity As String
          Dim strSpecificGravity, strInitialpH, strpH, strInitialViscosity, strViscosity As String
          Dim strViscosityDescription, strTorque, strViscosityMethod, strOOS, strAssayIngredient, strAssayPercentage, strNotes As String
          
    
    
    'strDate = "[DateofManufacture] > (#" & Me.S_Start_DateofManufacture & "# AND [DateofManufacture] < #" & Me.S_End_Date & "#)"
    strDate = "[DateofManufacture] between #" & Me.S_Start_DateofManufacture & "# AND #" & Me.S_End_Date & "#"
    'MsgBox strDate
    strSQL = strDate
          
    If Not IsNull(Me.S_ProductCategory) Then
       StrProductCategory = "[ProductCategory] like '*" & Me.S_ProductCategory & "*'"
       strSQL = strSQL & " AND " & StrProductCategory
    '   MsgBox strSQL
       
    End If
    
    
    If Not IsNull(Me.S_PurchaseOrder) Then
       strPurchaseOrder = "[PurchaseOrder] like '*" & Me.S_PurchaseOrder & "*'"
       strSQL = strSQL & " AND " & strPurchaseOrder
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_FormulatorLot) Then
       strFormulatorLot = "[FormulatorLot] like '*" & Me.S_FormulatorLot & "*'"
       strSQL = strSQL & " AND " & strFormulatorLot
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_CustomerName) Then
       strCustomerName = "[CustomerName] like '*" & Me.S_CustomerName & "*'"
       strSQL = strSQL & " AND " & strCustomerName
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_ProductName) Then
       strProductName = "[ProductName] like '*" & Me.S_ProductName & "*'"
       strSQL = strSQL & " AND " & strProductName
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_Ledger) Then
       strLedgerNumber = "[LedgerNumber] like '*" & Me.S_Ledger & "*'"
       strSQL = strSQL & " AND " & strLedgerNumber
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_ProductCode) Then
       strProductCode = "[ProductCode] like '*" & Me.S_ProductCode & "*'"
       strSQL = strSQL & " AND " & strProductCode
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_JulianDate) Then
       strJulianDate = "[JulianDate] like '*" & Me.S_JulianDate & "*'"
       strSQL = strSQL & " AND " & strJulianDate
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_Fragrance) Then
       strFragrance = "[Fragrance] like '*" & Me.S_Fragrance & "*'"
       strSQL = strSQL & " AND " & strFragrance
    '   MsgBox strSQL
    End If
    
    
    If Not IsNull(Me.S_Notes) Then
       strNotes = "[Notes] like '*" & Me.S_Notes & "*'"
       strSQL = strSQL & " AND " & strNotes
    '   MsgBox strSQL
    End If
    '   MsgBox strSQL
    
    
       strSQL = "SELECT DISTINCTROW N_T_BatchFinalSpecs.* FROM N_T_BatchFinalSpecs " & _
       "WHERE " & strSQL & _
        " Order By DateofManufacture DESC;"
      Me.RecordSource = strSQL
      Me.Requery
      Me.Repaint
    Me.AllowEdits = True
          Exit Sub
    
    
    End Sub
    
    
    Private Sub S_ProductCategory_Click()
      Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_Start_DateofManufacture_Click()
      Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_End_Date_Click()
        Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_PurchaseOrder_Click()
        Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_SoftwareLot_Click()
            Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_CustomerName_Click()
    Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_ProductName_Click()
     Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_Ledger_Click()
            Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_ProductCode_Click()
            Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_JulianDate_Click()
            Me.AllowEdits = True
    End Sub
    
    
    Private Sub S_Fragrance_Click()
      Me.AllowEdits = True
    End Sub
    
    
    
    
    Private Sub S_Notes_Click()
      Me.AllowEdits = True
    End Sub
    
    
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
      
      Select Case KeyCode
      
       Case vbKeyDown
        
        If CurrentRecord <> RecordsetClone.RecordCount Then
         DoCmd.GoToRecord , , acNext
        Else
         'Do nothing
        End If
        
       KeyCode = 0
       
       Case vbKeyUp
        
        If CurrentRecord <> 1 Then
         DoCmd.GoToRecord , , acPrevious
        Else
         'Do nothing
        End If
        
        KeyCode = 0
    End Select
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,614
    This is a form/subform? Subform in datasheet view? Use textboxes in subform footer with the aggregate calcs. More textboxes on main form refer to the hidden subform textboxes to display the calcs.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    acm007 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    12
    This is a sub-form inside a form in Form View. The sub-form is not really important to what I am trying to achieve. I need to create the Min, Max and Average of the two columns on the main form (pH, and Visc.). The complicated part it that it needs to refresh every time the search button on top is pressed.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,614
    Still, aggregate functions in textboxes.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    acm007 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    12
    How can I do that based on the results from the search boxes? If I just add the calculations it will do it for the all records (6714, shown above). I am not too experienced with Access, but would think that I need to add something to my code that would generate these statistics/calculation based on the data pulled for each specific search.

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,801
    you have been provided with the answer, your comments back seem to indicate it is not what is required. You have provided a fuzzy image of a number of rows and talk about searching. Our assumption (from your sql code if nothing else) is that the search is intended to filter the rows on the form - so the text boxes will adjust accordingly.

    You are saying 'If I just add the calculations it will do it for the all records (6714, shown above).' Which implies the search does not filter the rows in the form or you are making an erroneous assumption that filtering will not adjust and have not tried it.

    but would think that I need to add something to my code that would generate these statistics/calculation based on the data pulled for each specific search.
    why do you think that when by your own admission you are not too experienced?

    In summary, either we don't understand the question or you don't understand the answer.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,614
    Expressions in textboxes:

    =Min([pH])
    =Max(pH])
    =Avg([pH])
    =Min([Visc])
    =Max([Visc])
    =Avg([Visc])

    If form's records are filtered, the calcs will adjust.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Minimum & Maximum Row Value
    By Thompyt in forum Programming
    Replies: 8
    Last Post: 05-10-2016, 12:08 PM
  2. Replies: 2
    Last Post: 02-11-2015, 07:16 AM
  3. Replies: 1
    Last Post: 02-05-2015, 07:12 AM
  4. Minimum and Maximum Number
    By mohanmoni in forum Queries
    Replies: 3
    Last Post: 02-04-2015, 12:55 PM
  5. Can't see minimum or maximum buttons?!?
    By Felix_too in forum Forms
    Replies: 2
    Last Post: 12-29-2010, 09:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums