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
12
Medium Priority
?
316 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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…

577 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