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:
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