CODEWITHSUNDEEP
sqlms-accessvba
Stupid_Intern
Stupid_Intern

Reputation: 3450

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

Upvotes: 0

Views: 4008

Answers (2)

cenzuratu
cenzuratu

Reputation: 51

Private Sub Source_AfterUpdate() 

Dim rs As ADODB.Recordset, MyVal
Set rs = New ADODB.Recordset
rs.Open "SELECT MAX(Voucher_Number) from tblInvoiceLog where source="&me.source.value, CurrentProject.Connection
rs.MoveFirst
MyVal = rs.Fields(0).Value
Me.Voucher_Number.Value = MyVal + 1
rs.Close
Set rs = Nothing

End Sub

Upvotes: 1

Thomas G
Thomas G

Reputation: 10206

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

Upvotes: 2

Related Questions