We help IT Professionals succeed at work.

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

Sheogorath
Sheogorath asked
on
Medium Priority
412 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
Comment
Watch Question

Commented:
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;

Commented:
If you like what you see, then make that query the Control Source of your Combo Box.

Commented:
Sorry, that should be Row Source, not Control Source.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This is great! Thank you very much :)  ComboBox

Commented:
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 ;-)

Author

Commented:
Well I learned three approaches to achieve this. Thanks again to all.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

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

/gustav
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<Jeff and Ray, this is a callback function "selfgenerating" the values. No tables needed.>

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

;-)

Jeff

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Commented:
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.  
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> 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

Commented:
You need more than that!  - you missed the second-last day of the month.  
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
OK, but I guess you have figured out how:

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

/gustav

Commented:
Bingo!  Next thread.

Author

Commented:
Haha you guys are awesome :D
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.