Hello Stackoverflow community!
My question is in reguards to creating a custom column within a list box that is pulling from a table using SQL. Refering to the code and picture of the current list below, I want to create a custom column that is not stored in a table, that will be called "DaysActive" and will take todays date minus the StatusEffect Date for each individual record displayed and give the number of days in its own column, say between StatusEffect and Yr. Is this possible? Thank you in advance for taking the time to read through this.
Private Sub Form_Load()
Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT LastName, FirstName, Status, StatusEffect, Yr, Make, Model, VIN, Deduction, USLicense, RegistrationState, Dependents,Notes, ID FROM InsuranceTable" & _
"WHERE SentRegistration = False And Status IN ('active','add') Order By StatusEffect Desc "
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Set Me.lstInfo.Recordset = rs
lstnum = [lstInfo].[ListCount]
Me.lstcount.Value = lstnum - 1
DateDiff() function in your SQL.
DateDiff('d') returns the difference in days.
SELECT ..., StatusEffect, DateDiff('d',[StatusEffect],Date()) AS DaysActive, Yr, ...