Populate ComboBox with 15th & Last Day of Month

I'd like to populate a ComboBox on a form with the following dates (for use in selecting payroll dates):

15th & Last Day of Month

I'd like it to display these values for the previous month, current month & the next month, so it'd show 6 dates to select from, i.e.

02/15/2003
02/28/2003
03/15/2003
03/31/2003
04/15/2003
04/30/2003
pcrouseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HobsonTCommented:
Add this to your forms on open event:

  Dim strSource As String

  strSource = Chr$(34) & DateSerial(Year(Now()), Month(Now()) - 1, 15) & Chr$(34)
  strSource = strSource & "," & Chr$(34) & DateSerial(Year(Now()), Month(Now()), 1) - 1 & Chr$(34)
  strSource = strSource & "," & Chr$(34) & DateSerial(Year(Now()), Month(Now()), 15) & Chr$(34)
  strSource = strSource & "," & Chr$(34) & DateSerial(Year(Now()), Month(Now()) + 1, 1) - 1 & Chr$(34)
  strSource = strSource & "," & Chr$(34) & DateSerial(Year(Now()), Month(Now()) + 1, 15) & Chr$(34)
  strSource = strSource & "," & Chr$(34) & DateSerial(Year(Now()), Month(Now()) + 2, 1) - 1 & Chr$(34)

  Me.cboDates.RowSource = strSource


Make sure that the Row Source Type for the combo box is set to Value List
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
or, if you don't want to have to HARD-CODE the strSource, you could do it entirely in code:


Dim iMonth as Integer

with cboDates
   .RowSource = ""
   .RowSourceType = "Value List"
   for iMonth = 1 to 12
     .RowSource = .rowsource & DateSerial(Year(Date()),IMonth, 15) & "," & DateSerial(Year(Date()),IMonth + 1,0) & ","
   next
end with


the trick here is that DateSerial(Year,Month,0) give that Last day of the previous month!!!

AW
0
1WilliamCommented:
I agree with Arthur, however, if the month is say January, what if the user wants to see December?  I'd reccomend adding some more code to allow for a few years spread, say +- one year.  What do you think, Arthur?

'Nothing worse than a back seat programmer' ;-)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcrouseAuthor Commented:
It'd be nice to see weeks for December, when it's the first week in January, as you know people never do anything on time.  If this is possible I'd really appreciate any further elaboration.  Thanks a bunch.
0
pcrouseAuthor Commented:
Thanks for your help, this does exactly what I need.  I'll check out Arthur's solution too when I geta chance.
0
Arthur_WoodCommented:
ok, so you select a Year range as well, no biggie:

Dim iMonth as Integer
Dim iYear as Integer

with cboDates
  .RowSource = ""
  .RowSourceType = "Value List"
  for iYear = Year(Date())-1 to Year(Date())+1
     for iMonth = 1 to 12
       .RowSource = .rowsource & DateSerial(IYear,IMonth, 15) & "," & DateSerial(IT+Year,IMonth + 1,0) & ","
     next
  Next
end with


  this then goes from Last Year to Next year...

AW
0
pcrouseAuthor Commented:
Well Arthur, since you seem to be in a giving mood, how would one list the current Day, thru the end of the previous month, in consecutive order (in a ComboBox).

Thanks.

i.e.
All dates from February... (Previous Month)
03/01/2003
03/02/2003
03/03/2003
03/04/2003
03/05/2003
03/06/2003
03/07/2003
03/08/2003
03/09/2003
03/10/2003
03/11/2003
03/12/2003
03/13/2003 (Current Date)
0
pcrouseAuthor Commented:
Well Arthur, since you seem to be in a giving mood, how would one list the current Day, thru the end of the previous month, in consecutive order (in a ComboBox).

Thanks.

i.e.
All dates from February... (Previous Month)
03/01/2003
03/02/2003
03/03/2003
03/04/2003
03/05/2003
03/06/2003
03/07/2003
03/08/2003
03/09/2003
03/10/2003
03/11/2003
03/12/2003
03/13/2003 (Current Date)
0
Arthur_WoodCommented:
and what am I to expect in return for such largess???  you want something for nothing -Lots of Luck. LOL

you should be able to figure it out for yourself, as it is actually rather straighforward, though clearly somewhat different from the current case.

AW
0
1WilliamCommented:
I tried to gently tell him, Arthur. Your code was the right direction......
0
pcrouseAuthor Commented:
Thanks.  I'm playing with both of the code sets to get it to do what I want.
0
pcrouseAuthor Commented:
Okay here's my code to list today's date & 60 previous days in a ComboBox.

Dim strSource1 As String, strDate As Date, n As Integer
strDate = Format(Now, "Short Date")
strSource1 = strDate
Do While n <= 60
    strDate = strDate - 1
    strSource1 = strSource1 & "," & strDate
    n = n + 1
Loop

Me.cboWorkDay.RowSource = strSource1


Thanks to help from our programming consultant we have working on another project.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.