Select Maximum value from a table field with criteria

Question

This gets the maximum number from the

Voucher_Number

field of Table

tblInvoiceLog

I want to get the maximum number but only where the field

Source

in the same table is equal to

Me.Source.Value

I am not sure how can I add this condition please advise:

CODE:

Private Sub Source_AfterUpdate()    



    Dim rs As ADODB.Recordset, MyVal

    Set rs = New ADODB.Recordset

    rs.Open "SELECT MAX(Voucher_Number) from tblInvoiceLog", CurrentProject.Connection

    rs.MoveFirst

    MyVal = rs.Fields(0).Value

    Me.Voucher_Number.Value = MyVal + 1

    rs.Close

    Set rs = Nothing



End Sub

Answer 1

You should add a WHERE clause to your SQL query

Private Sub Source_AfterUpdate()    

    Dim rs As ADODB.Recordset, MyVal

    Dim SQL as String

    Set rs = New ADODB.Recordset

    ' If Source field is STRING type
    SQL = "SELECT MAX(Voucher_Number) from tblInvoiceLog WHERE [Source]='" & Me.Source.Value & "'"

    ' If Source field is NUMBER type
    SQL = "SELECT MAX(Voucher_Number) from tblInvoiceLog WHERE [Source]=" & Me.Source.Value 

    rs.Open SQL, CurrentProject.Connection
    rs.MoveFirst
    MyVal = rs.Fields(0).Value
    Me.Voucher_Number.Value = MyVal + 1
    rs.Close
    Set rs = Nothing

End Sub

Select Maximum value from a table field with criteria

Sign up