Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Populate ComboBox with 15th & Last Day of Month

Posted on 2003-03-12
Medium Priority
316 Views
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
Question by:pcrouse
• 6
• 3
• 2
• +1

LVL 2

Accepted Solution

HobsonT earned 200 total points
ID: 8125514

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

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

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

Author Comment

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

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

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

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

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

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

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

Author Comment

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

Author Comment

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

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
###### Suggested Courses
Course of the Month15 days, 4 hours left to enroll