Auto populate with weekly date on Combo box

[jepoysaipan]

Hi,

I am in need of help regarding the current application I am doing, I have a weekly report tab whereas a user can query his/her weekly accomplished projects, the problem is that I want a combo box to populate with weekly dates within a year, so that the user only select his/her ID and then select a week on the combo box and execute a query based on the selected week.

Thanks in advance!

 

[GolferGuy]

What date do you want to start with? Or what day of the week? How far back do you want the combo box to go with possible choices? Do you always want the date in the combo box to be in history, or is there a place during the week the date in the combo box should be today's date? Those answers will help us help you.

 

[jepoysaipan]

Hi GolferGuy,

Originally, I wanted a combo box based on the starting date on one field of the table I have then increment it until the last date entry. It was done perfectly (since I just bound the "Starting Date From" & "End Date To" to the Date field I had on the table.

But, upon deploying the application to the user for testing they suggested that they wanted to select only on a single combo box that is populated with weeks depending on the data on the table (e.g. Week - 02/04/08 to 02/08/07 and so on....)

Thanks.

 

[GolferGuy]

so, you want the week to go from Feb 4, 2008 to Feb 8, 2007? I'm really thinking you want the week to show from Monday through Friday, and to start with the earliest date in some table. What dates do you want at the top of the list? The most current dates, or the oldest dates in the table?

is there a place during the week the date in the combo box should be today's date?

Do you want the current week in the combo box?

 

[jepoysaipan]

I do want the the last week on the combo box, below is the visual example I want to do with the box

12/31/07 - 01/06/08
01/07/08 - 01/13/08
01/20/08 - 01/20/08
01/21/08 - 01/27/08

Assuming that the first date field has the value of 12/31/07 and the last was 01/27/08, eventually as the data gets bigger so does the combo box value.

 

[GolferGuy]

This looked like a fun challeng, so here is the code.

Function WeeksToSelect(TableName, DateFieldName) As String
'This routine will accept the table name and name of the date field.
'This routine will find the oldest date in the table, in the field named, and then return a string of weeks,
'  separated by a semi-colon for all the weeks represented in the dates within the table.
Dim OldestDate As Date
Dim NewestDate As Date
Dim LastMondayUsed As Date
Dim strWeek As String
Dim AllWeeks As Variant
Dim RetVal As Variant       'used as a work variable
  OldestDate = DMin(DateFieldName, TableName)
  RetVal = Format(OldestDate, "w", vbMonday)      '1 = Monday, 2 = Tuesday, etc.
  RetVal = DateAdd("d", RetVal - 1, OldestDate)   'This will get the closest Monday to the oldest date
  LastMondayUsed = DateAdd("d", -7, RetVal)       'This gives us a starting point one week before where we want to start.
  NewestDate = DMax(DateFieldName, TableName)
                                                  'All initial values have been calculated.
  
'Now, loop through this code building the list of weeks to be returned to the caller.  This string will then
'  be placed into the RowSource of the combo box by the caller.
  AllWeeks = Null                                  'Set to null for later
  While LastMondayUsed <= NewestDate
'Now, we will calculate the next week days to return as one week for the combo box
    LastMondayUsed = DateAdd("d", 7, LastMondayUsed)  'This will give us the Monday date for the current week
'If the new LastMondayUsed is larger than the NewestDate in the table, return an empty string ("") to
'  signify we have reached the end of the line.
    strWeek = Format(LastMondayUsed, "mm/dd/yyyy")    'Put the Monday date into the work string
'The next line will build the Week to be returned to the caller of this function
'  By adding 4 to the Monday date we get the Friday date for the complete week
    strWeek = strWeek & " - " & Format(DateAdd("d", 4, LastMondayUsed))
    AllWeeks = AllWeeks + ";" & strWeek
  Wend
  WeeksToSelect = AllWeeks
End Function

In the OnLoad event of the form, put a call to this function. This function will return the list of weeks in the correct order, and formated to be put directly into the RowSource for the combo box. Be sure to set the Row Source Type of the combo box to be "Value List".
The call to this routine should look something like this:
Me.YourComboBox.RowSource = WeeksToSelect("YourTable", "YourDateFieldName")

Hope this helps,

 

[jepoysaipan]

Hi Ron K,

Tried the code but have this error.

Thanks.

 

[GolferGuy]

That means you need to name the module that you put the code into some other name. I would suggest you put "bas" in the front of the name that it already has. Therefore, it would be named "basWeeksToSelect"

By the way, who is Ron K?

 

[jepoysaipan]

WOW! ^^

Just what I needed, works 100% PERFECT! ^^

Just a quick note, how do I get the exact week on the combo box? meaning not based on the records.

And another one, how do I design a query based on this value?

Thanks again!

 

[GolferGuy]

If you notice, the first 10 bytes of the selected row have the start date of the week, and the last 10 bytes of the same row have the end date of the week. By just referencing the list box after you have selected a specific week, what Access returns to your reference is the whole row. So, by using Left(ListBoxName, 10) you get the start date. And by using Right(ListBoxName, 10) you get the end date. If you are doing that in a query, the the criteria row for the date column would look something like this:
Between Left(Forms!YourFormName!ListBoxName, 10) AND Right(Forms!YourFormName!ListBoxName, 10)

Just a quick note, how do I get the exact week on the combo box? meaning not based on the records.

I'm not sure I answered this question because I'm not sure what you are asking. If it is not answered, please rephrase your question.

 

[jepoysaipan]

Sorry for the confusion, what I intend to ask is: If I want the combo box to populate every week of the months for the last year up to the end of the year. Pls. see visual refference below:

Combo box value:

01/01/2007 - 01/07/2007
01/08/2007 - 01/14/2007
...
12/31/2008 - 01/07/2008
...
12/29/2008 - 01/04/2009

Thanks.

 

[DCrake]

Simple Software Solutions

There is a function called ListMondays below that will solve your problem

On your form place a combo box and in the RowSourceType type in ListMondays

You can amend the start date to be what you want in this example it starts two months previous to today.

Function ListMondays(Fld As Control, id As Variant, _
row As Variant, col As Variant, Code As Variant) _
As Variant
Dim intOffset As Integer
Dim szDate As Date
szDate = DateAdd("m", -2, Now())

Select Case Code
Case acLBInitialize ' Initialize.
ListMondays = True
Case acLBOpen ' Open.
ListMondays = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListMondays = 9
Case acLBGetColumnCount ' Get columns.
ListMondays = 1
Case acLBGetColumnWidth ' Get column width.
ListMondays = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intOffset = Abs((9 - Weekday(szDate)) Mod 7)
ListMondays = Format(szDate + _
intOffset + 7 * row, "dd/mm/yyyy")
End Select
End Function


Code Master:http://www.icraftlimited.co.uk

 

[GolferGuy]

If you look through the routine I posted, you will see that it is getting the start date for all these Mondays from the earliest date in your table. If that is not what you want, then all you need to do is provide, within that area of the code, the actual Monday that you want the weeks to start at. Below is the original code I posted, with a change in the week display dates. I changed the date to from Friday to Sunday. I had asked the question about Monday through Friday rather than Monday through Sunday which you answered with an example rather than words and I missed the detail of the example. The other thing I have changed in this code is to put a comment in the code where you can change the code to be the Monday that you want the dates to start at. The added or changed parts of the code are in Red

Function WeeksToSelect(TableName, DateFieldName) As String
'This routine will accept the table name and name of the date field.
'This routine will find the oldest date in the table, in the field named, and then return a string of weeks,
'  separated by a semi-colon for all the weeks represented in the dates within the table.
Dim OldestDate As Date
Dim NewestDate As Date
Dim LastMondayUsed As Date
Dim strWeek As String
Dim AllWeeks As Variant
Dim RetVal As Variant       'used as a work variable
[COLOR="Red"]'Change the next line to give this routine a fixed starting Monday
'Suggestion:  OldestDate = #Jan 1, 2007# 'or whatever date you choose[/COLOR]
  OldestDate = DMin(DateFieldName, TableName)
  RetVal = Format(OldestDate, "w", vbMonday)      '1 = Monday, 2 = Tuesday, etc.
  RetVal = DateAdd("d", RetVal - 1, OldestDate)   'This will get the closest Monday to the oldest date
  LastMondayUsed = DateAdd("d", -7, RetVal)       'This gives us a starting point one week before where we want to start.
  NewestDate = DMax(DateFieldName, TableName)
                                                  'All initial values have been calculated.
  
'Now, loop through this code building the list of weeks to be returned to the caller.  This string will then
'  be placed into the RowSource of the combo box by the caller.
  AllWeeks = Null                                  'Set to null for later
  While LastMondayUsed <= NewestDate
'Now, we will calculate the next week days to return as one week for the combo box
    LastMondayUsed = DateAdd("d", 7, LastMondayUsed)  'This will give us the Monday date for the current week
'If the new LastMondayUsed is larger than the NewestDate in the table, return an empty string ("") to
'  signify we have reached the end of the line.
    strWeek = Format(LastMondayUsed, "mm/dd/yyyy")    'Put the Monday date into the work string
'The next line will build the Week to be returned to the caller of this function
'  By adding [COLOR="Red"]6[/COLOR] to the Monday date we get the [COLOR="Red"]Sunday[/COLOR] date for the complete week
    strWeek = strWeek & " - " & Format(DateAdd("d", [COLOR="Red"]6[/COLOR], LastMondayUsed))
    AllWeeks = AllWeeks + ";" & strWeek
  Wend
  WeeksToSelect = AllWeeks
End Function

 

[jepoysaipan]

GolferGuy,

Hit the right mark! amazing.

Thanks a lot.

Now I just have to figure out how to get the "OldestDate" to start automatically a year behind the current date (not manually coded).

Cheers!

 

[GolferGuy]

OldestDate = DateAdd("yyyy", -1, Date())

 

[jepoysaipan]

Awesome! ^^

 

[jepoysaipan]

Here's another one, what do I need to change the order of the "AllWeeks", right now the starting value is exactly the first week of the the last year, now I want the combo to show the present week going to the last week of last year. pls. see below for your refference:

Present value of combo box:

02/25/2007 - 03/03/2007
.....
02/17/2008 - 02/23/2008


What I need:
02/17/2008 - 02/23/2008
....
02/25/2007 - 03/03/2007

Basically it's the vice versa of the present value.

Thanks in advance!