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

x
?
Solved

Listing the last and 15th day of every month in a combo box.

Posted on 2011-04-24
18
Medium Priority
?
383 Views
Last Modified: 2012-06-27
I have issues getting this code work. I have been able to link it to a fill box and generate the dates but only either the 15th of each month is listed  or the last day of the month. Below is the code I have been working on. any help is very much appreciated.

Function ListMondays(fld As Control, id As Variant, _
    row As Variant, col As Variant, code As Variant) _
     As Variant
    Dim intOffset As Integer
    Dim temp As Integer

        temp = 0
   

    Select Case code
        Case acLBInitialize            ' Initialize.
            ListMondays = True
        Case acLBOpen                    ' Open.
            ListMondays = Timer        ' Unique ID.
        Case acLBGetRowCount            ' Get rows.
            ListMondays = 10
        Case acLBGetColumnCount    ' Get columns.
            ListMondays = 1
        Case acLBGetColumnWidth    ' Get column width.
            ListMondays = -1            ' Use default width.
        Case acLBGetValue                ' Get the data.
            'ListMondays = DateSerial(Year(Date), Month(Date) + 1 + row, 1) - 1
        'Case acLBGetFormat
            'ListMondays = "mm/dd/yyyy"
           
            'intOffset = Abs((9 - Weekday(Now)) Mod 7)
           
           Select Case temp
           Case Abs((temp) Mod 2) = 0
                ListMondays = DateSerial(Year(Date), Month(Date) + temp, 1) - 1
                'row = row + 1
                'ListMondays = DateSerial(Year(Date), Month(Date) + temp, 1) + 14
           Case Abs((temp) Mod 2) = 1
               ListMondays = DateSerial(Year(Date), Month(Date) + row, 1) + 14
                           
          End Select
           
            temp = temp + 1
                       
    End Select
0
Comment
Question by:Sheogorath
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 35457635
If you want this for say a two year period, you need a small table Nums with an integer field Num containing the values 0-23.  Then run this query:

SELECT DateSerial(Year(Date), Month(Date) + Num, 15) as MidMonthDate,
DateSerial(Year(Date), Month(Date)+Num+1,0) As EndMonthDate FROM Nums;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35457641
If you like what you see, then make that query the Control Source of your Combo Box.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35457643
Sorry, that should be Row Source, not Control Source.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35472675
Since you did not post any source data, I am not sure how any dates are represented in your DB...

But if you have a table (or query) listing all sequential dates, then something roughly like this will list all the 15ths and all the last days of each month, in a single column, in a combobox:

SELECT DISTINCT DateSerial(Year([YourDateField]),Month([YourDateField])+1,0) AS 15ofMonthOrEOM
FROM YourTable
UNION SELECT IIf(Day([YourDateField])=15,[YourDateField],Null)
FROM YourTable
WHERE (((IIf(Day([YourDateField])=15,[YourDateField],Null)) Is Not Null));

This can probably be simplified, ..but it works as-is...

Sample attached

JeffCoachman
Access-EEQ26974756-List15th-And-.mdb
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 35473190
Jeff and Ray, this is a callback function "selfgenerating" the values. No tables needed.

Here is how to obtain the list:
Public Function ListFifteenthAndLastDayOfMonthInYear( _
  ctl As Control, _
  lngId As Long, _
  lngRow As Long, _
  lngCol As Long, _
  intCode As Integer) As Variant
  
  ' 2011-04-27. Cactus Data ApS, CPH.

  ' Count of months.
  Const cintMonths    As Integer = 12
  ' Fifteenth day of month.
  Const cintFifteenth As Integer = 15
  ' Last day of month.
  Const cintLastDay   As Integer = 0
  ' Format for ListBox.
  Const cstrFormat    As String = "yyyy\-mm\-dd"
  
  Static intYear      As Integer
  Static strFormat    As String
  
  Dim varValue        As Variant
  
  Select Case intCode
    Case acLBInitialize
      intYear = Year(Date)
      If ctl.ControlType = acComboBox Then
        ' Use format of control.
        strFormat = ctl.Format
      Else
        ' Use predefined format.
        strFormat = cstrFormat
      End If
      varValue = True             ' True to initialize.
    Case acLBOpen
      varValue = Timer            ' Autogenerated unique ID.
    Case acLBGetRowCount          ' Get rows.
      varValue = cintMonths * 2   ' Set number of rows.
    Case acLBGetColumnCount       ' Get columns.
      varValue = 1                ' Set number of columns.
    Case acLBGetColumnWidth       ' Get column width.
      varValue = -1               ' Use default width.
    Case acLBGetValue             ' Get the data.
      If lngRow Mod 2 = 0 Then
        ' Get the fifteenth day of this month.
        varValue = DateSerial(intYear, Int(lngRow / 2) + 1, cintFifteenth)
      Else
        ' Get the last day of this month.
        varValue = DateSerial(intYear, -Int(-lngRow / 2) + 1, cintLastDay)
      End If
    Case acLBGetFormat            ' Format the data.
      varValue = strFormat        ' Apply format of the generated strings.
    Case acLBEnd
      ' Do something when the form with the control closes or
      ' the control is requeried.
  End Select
  
  ' Return Value.
  ListFifteenthAndLastDayOfMonthInYear = varValue

End Function

Open in new window

/gustav
0
 

Author Comment

by:Sheogorath
ID: 35475645
This is great! Thank you very much :)  ComboBox
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35475913
Gustav:  Hi, I appreciate what code can do - I just prefer to minimize the use of code in db development.  I guess I'm a bit quirky that way ;-)
0
 

Author Comment

by:Sheogorath
ID: 35475963
Well I learned three approaches to achieve this. Thanks again to all.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35476015
You are welcome!

Callback functions is one of the gems of Access. Perfect and fast for tasks like this.

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35487746
<Jeff and Ray, this is a callback function "selfgenerating" the values. No tables needed.>

As always...
    "Everyday is a school day"

;-)

Jeff
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35487858
I still say a simple table with 24 rows and a small query is better than 60 lines of code in terms of maintenance - in a DB environment.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35489401
But Ray, there is no maintenance for this callback function.
And a simple table wouldn't do it; the last day of February changes between 28 and 29.

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35494013
Gustav:  This is just a case of individual preference.  BTW, my approach handles leap years.  By maintenance I thinking in four years time after the database designer has moved on the boss decides he wants it on the 14th and second last day of every month - I just change two values in the query.  
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35494919
> I just change two values in the query.  

And I just change one!

 ' Fifteenth day of month minus one.
  Const cintFifteenth As Integer = 14

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35495177
You need more than that!  - you missed the second-last day of the month.  
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35495306
OK, but I guess you have figured out how:

  ' Last day of month minus one.
  Const cintLastDay   As Integer = -1

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35495622
Bingo!  Next thread.
0
 

Author Comment

by:Sheogorath
ID: 35495847
Haha you guys are awesome :D
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

581 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