Custom date format for custom combo box value list

[merry_fay]

Hi,

I've written some vba to set a combo box value list as last month or the month before. Now I'm trying to format it & am having issues.

I've set the combo box itself to be mmm yyyy & once a selection is made, it displays like that, but the dropdown is still showing eg 01/02/2011. What do I need to do to get it to show in the dropdown as mmm yyyy while still being set as a date (needed for later)?
This is my code (please don't laugh!!!! I try!):

Dim myDateArray(1) As String, myDateList As String, i As Integer, m As Integer, f As Integer, subi As Integer
If Int(VBA.Year(Now()) / 4) = VBA.Year(Now()) / 4 Then
f = 29
Else
f = 28
End If

For i = 0 To 1
If i = 0 Then
subi = 13
Else
subi = m
End If
m = VBA.Month(Now())
myDateArray(i) = CStr(VBA.DateSerial(VBA.Year(Now()), m, 1) - Choose(subi, 30, 31, 31, f, 31, 30, 31, 30, 31, 31, 30, 31, 0) - Choose(m, 31, 31, f, 31, 30, 31, 30, 31, 31, 30, 31, 30))
Next i
myDateList = Join(myDateArray, ";")

Me!cmbYr.RowSource = myDateList


Thanks

 

[DCrake]

Are you trying to get the combo to list dates for the current or previous month?

What values to do want to show for each date?

 

[merry_fay]

From a visibility perspective, as it's currently March, I want them to have the dropdown option for:
Feb 2011
Jan 2011

From a behind the scenes perspective, I need the selected option to be the 1st of the chosen month/year combo, 00:00:00 time.

This will be used as an exported column header for excel to then do it's stuff on.

Thanks
merry_fay

 

[DCrake]

Function FirstDayOfLastMonth() As Date
    Dim d As Date
    d = CDate("01/" & Month(Date) & "/" & Year(Date))
    FirstDayOfLastMonth = DateAdd("m", -1, d)
End Function

Function LastDayOfLastMonth() As Date
    LastDayOfLastMonth = DateAdd("d", -1, CDate("01/" & Month(Date) & "/" & Year(Date)))
End Function

Try using these functions

To populate the combo you would need

For x = 1 to 3

  DtmString = DtmString & ";" & Format(DateAdd("m",- x,Date(),"mmm yyyy")

Next
DtmString = mid(DtmString,2)

Me.Combo.RowSource = DtmString

 

[merry_fay]

Hiya,

I'll give that a go thanks.
Just as a slight curve ball though, does it take into account previous years, eg in January coming up with Nov & Dec of the previous year?

Thanks
merry_fay

 

[DCrake]

Yes it does, If you look at the DateAdd element it is using a minus x value thus the current month less 1 then 2 then 3

 

[merry_fay]

Hiya,

I'll give that a go thanks.
Just as a slight curve ball though, does it take into account previous years, eg in January coming up with Nov & Dec of the previous year?

Thanks
merry_fay

Aha, awesome, it does thankyou!! Might have to go & update some of my rickety code with the new toy!!

Just a little problem though -1st it won't let me use it as a field name & 2nd, when I put it into a table, it comes out as a very small decimal number. I'm used to dates using the 5 digit (eg today=40630) values.

Thanks
merry_fay

 

[DCrake]

Just a little problem though -1st it won't let me use it as a field name & 2nd, when I put it into a table, it comes out as a very small decimal number. I'm used to dates using the 5 digit (eg today=40630) values.

Can you elaborate

 

[merry_fay]

In this particular section I'm dealing with linear regression for forecasting.

There's inputs from a number of sources, mapped in the database & then this form will spew them out into excel & run a macro there to do the linear regression bit then bring the output of that back in (I investigated & found it was recommended to do linear regression in excel...).
This is why the date & it's format are so important. I need a trigger in excel to tell it which month is being dealt with.

Having the date as mmm yyyy is the nice to have bit here -just looks so much better when someone's dealing with 'Feb 2011' rather than '01/02/2011'. Having the date as 1st of the month in the output is the crucial part. Even eg for 1st Feb, 40575 as the field name for the table to be exported would be good.

Thanks
merry_fay

 

[DCrake]

1st it won't let me use it as a field name & 2nd, when I put it into a table

1. What won't it let you use as a field name?
2. When you put what in a table?

How are you employing the code I sent you?

 

[merry_fay]

Woohoo, got there!!

Private Sub Form_Load()
Dim x As Integer
Dim DtmString As String
Dim d As Date

For x = 1 To 2

d = CDate("01/" & VBA.Month(Date) & "/" & VBA.Year(Date))
DtmString = DtmString & ";" & Format(DateAdd("m", -x, d), "mmm yyyy")
Next

DtmString = Mid(DtmString, 2)
Me!cmbYr.RowSource = DtmString

End Sub

then

Private Sub btnTape_Click()
DoCmd.RunSQL "SELECT 'xxx' AS " & Format(Me!cmbYr, 0) & " INTO Test;"
End Sub

Thanks for your help!!