I have a table, tblFees, that has the following columns:
ID = AutoNumber
AccountNumber = 8 AlphaNumeric digits
FeeChargedEffectiveDate = Short Date
DateEntered = Date and Time
I'm using the following to count how many times an account number chosen from a user form appears in the table, tblFees. If it appears only once then that record should be selected to populate fields on the user form. If the account number appears more than once then the record with the most recent FeeChargedEffectiveDate should be selected.
In the outside chance that there are multiple records with the same FeeChargedEffectiveDate then it should select the record with the most recent DateEntered.
I need help working with the dates, can't figure out what is the best way to handle this.
ID = AutoNumber
AccountNumber = 8 AlphaNumeric digits
FeeChargedEffectiveDate = Short Date
DateEntered = Date and Time
I'm using the following to count how many times an account number chosen from a user form appears in the table, tblFees. If it appears only once then that record should be selected to populate fields on the user form. If the account number appears more than once then the record with the most recent FeeChargedEffectiveDate should be selected.
In the outside chance that there are multiple records with the same FeeChargedEffectiveDate then it should select the record with the most recent DateEntered.
Code:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim AccountCount As Long
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from tblFees Where [AccountNumber] ='" & Me.cboAccountNumber & "'", dbOpenDynaset)
rst.MoveLast
AccountCount = rst.RecordCount
Debug.Print AccountCount
rst.Close
Set rst = Nothing
Set dbs = Nothing
I need help working with the dates, can't figure out what is the best way to handle this.