Dynamically populate combobox with Month-Year values

[Sck]

I "think" this should be easy but my brain is not getting me there.... I need to populate a combobox with the 15th of the last 3 months and the next 3 months but I need it to be formatted (displayed) as the month - year to the user.



example:
value stored:
8/15/2018
9/15/2018
10/15/2018
11/15/2018
….

Displayed as:
Aug - 2018
Sep - 2018
Oct - 2018
Nov - 2018
….

the combobox needs to update the months based on the current month. When I store the value into a table I need the mm/dd/yyyy value stored so I can query off of it.

[ranman256]

in the query the combo uses:
select the DateFld you want to display,
in the property FORMAT , set to : mmm-yyyy

NOTE that this will not sort correctly.
I use: yyyy-mm mmm

you get
2018-01 Jan
2018-02 Feb
etc

tho the query can have 2 fields: 1 to show , 1 to sort
show: Format(DateFld,mmm yyyy ), sort on DateFld

[Sck]

issue I am having is I cant figure out how to write the query as I am not pulling from a table. I just want the 6 dates displayed and have them adjust based on the current date.

[ranman256]

you really should use a table.
use this code to create the values for the table.
it puts 6 dates into the tDates table, starting with today.

Public Sub MakeBlockOfDates()
Dim vDat
Dim sSql As String


DoCmd.SetWarnings False
   'clear the target table
sSql = "DELETE * FROM tDates"
DoCmd.RunSQL sSql


vDat = Date
For i = 1 To 6
    sSql = "insert into tDates ([StartDte]) values (#" & vDat & "#)"
    DoCmd.RunSQL sSql
    
    vDat = DateAdd("d", -1, vDat)  'get 1 day before
Wend
DoCmd.SetWarnings True
DoCmd.OpenTable "tDates"
End Sub

[Sck]

thanks that is the route I went but I wanted to avoid using a table as it just felt like an unneeded step in the process.

[moke123]

with a value list combo box you could try

Me.Combo0.RowSource = ""
 
 Dim i As Integer, dte As Date, x As Variant
 
 x = Split("-3,-2,-1,0,1,2,3", ",")
 
 For i = 0 To 6
 
 dte = DateAdd("m", x(i), Date)
 
 Me.Combo0.AddItem Format(dte, "mmm - yyyy")
  
 Next i