Listing the last and 15th day of every month in a combo box.
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
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
If you like what you see, then make that query the Control Source of your Combo Box.
Sorry, that should be Row Source, not Control Source.
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([YourDateF
FROM YourTable
UNION SELECT IIf(Day([YourDateField])=1
FROM YourTable
WHERE (((IIf(Day([YourDateField]
This can probably be simplified, ..but it works as-is...
Sample attached
JeffCoachman
Access-EEQ26974756-List15th-And-.mdb
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([YourDateF
FROM YourTable
UNION SELECT IIf(Day([YourDateField])=1
FROM YourTable
WHERE (((IIf(Day([YourDateField]
This can probably be simplified, ..but it works as-is...
Sample attached
JeffCoachman
Access-EEQ26974756-List15th-And-.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is great! Thank you very much :) 
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 ;-)
ASKER
Well I learned three approaches to achieve this. Thanks again to all.
You are welcome!
Callback functions is one of the gems of Access. Perfect and fast for tasks like this.
/gustav
Callback functions is one of the gems of Access. Perfect and fast for tasks like this.
/gustav
<Jeff and Ray, this is a callback function "selfgenerating" the values. No tables needed.>
As always...
"Everyday is a school day"
;-)
Jeff
As always...
"Everyday is a school day"
;-)
Jeff
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.
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
And a simple table wouldn't do it; the last day of February changes between 28 and 29.
/gustav
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.
> 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
And I just change one!
' Fifteenth day of month minus one.
Const cintFifteenth As Integer = 14
/gustav
You need more than that! - you missed the second-last day of the month.
OK, but I guess you have figured out how:
' Last day of month minus one.
Const cintLastDay As Integer = -1
/gustav
' Last day of month minus one.
Const cintLastDay As Integer = -1
/gustav
Bingo! Next thread.
ASKER
Haha you guys are awesome :D
SELECT DateSerial(Year(Date), Month(Date) + Num, 15) as MidMonthDate,
DateSerial(Year(Date), Month(Date)+Num+1,0) As EndMonthDate FROM Nums;