?
Solved

Populate ComboBox with 15th & Last Day of Month

Posted on 2003-03-12
12
Medium Priority
?
307 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:pcrouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 2

Accepted Solution

by:
HobsonT earned 200 total points
ID: 8125514
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8127171
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
 
LVL 18

Expert Comment

by:1William
ID: 8127747
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:pcrouse
ID: 8128374
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
 

Author Comment

by:pcrouse
ID: 8128631
Thanks for your help, this does exactly what I need.  I'll check out Arthur's solution too when I geta chance.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8129601
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
 

Author Comment

by:pcrouse
ID: 8129810
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
 

Author Comment

by:pcrouse
ID: 8130324
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8131124
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
 
LVL 18

Expert Comment

by:1William
ID: 8131139
I tried to gently tell him, Arthur. Your code was the right direction......
0
 

Author Comment

by:pcrouse
ID: 8131280
Thanks.  I'm playing with both of the code sets to get it to do what I want.
0
 

Author Comment

by:pcrouse
ID: 8132135
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question