Ace16150s Ace16150s - 1 year ago 130
SQL Question

MS Access VBA - Custom Column is ListBox (using a value from another column coming from a table)

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()

DoCmd.RunCommand acCmdWindowHide

Dim rs As Recordset
Dim strSQL As String
Dim lstnum

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
End Sub

Link to picture of my current list since I do not have enough rep points to embed it ;/

Answer Source

Use the DateDiff() function in your SQL.
DateDiff('d') returns the difference in days.

SELECT ..., StatusEffect, DateDiff('d',[StatusEffect],Date()) AS DaysActive, Yr, ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download