newguy newguy - 1 month ago 10
SQL Question

Select Maximum value from a table field with criteria

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

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