Hi folks,
I have a simple table with records in and a date field for when the record was entered.
On my form I have set up a date filter (2 x text boxes) where users enter a start date in the 1st box and enter an end date in the 2nd box, basically they click a button and it returns the correct info (great)
Users have requested to select a weeknumber to generate their data aswell as have the ability to manualy add dates(for filters over a week).
Ideally what i'm after is a way to auto populate my 2 x date boxes (start and end of week dates) upon the selection of a weeknumber from a combobox or a textbox.
Hope this is clear
Many many thanks
TGK
Full Version: Generate dates from a selected weeknumber
If you're planning on using a combo box, why not include the start and end dates as columns in the combo box? In that way, you simply copy them from the selected row to the text boxes on the form.
(Edit i forget my manners)
Thanks for your response Doug
However,
Don't really know what you mean.
Here is the code i'm currently using as a filter for the data within my table:
Sub Management()
Dim OpenManagement As Integer
Dim ClosedManagement As Integer
Dim AClosedManagement As Integer
Dim dtDateFrom As Date
Dim dtDateTo As Date
dtDateFrom = CDate([Forms]![SuggestionSummary]![DateFrom])
dtDateTo = CDate([Forms]![SuggestionSummary]![DateToo])
'sum of filtered criteria between dates selected on form
OpenManagement = Nz(DCount("[ID]", "tblSuggestions", "[OpenClosed] = 'Open' and Group = 'Management' and [tblSuggestions]![Date] Between #" & Format(dtDateFrom, "mm/dd/yyyy") & "# and #" & Format(dtDateTo, "mm/dd/yyyy") & "#"), 0#)
ClosedManagement = Nz(DCount("[ID]", "tblSuggestions", "[OpenClosed] = 'Closed' and Group = 'Management' and [tblSuggestions]![Date] Between #" & Format(dtDateFrom, "mm/dd/yyyy") & "# and #" & Format(dtDateTo, "mm/dd/yyyy") & "#"), 0#)
AClosedManagement = Nz(DCount("[ID]", "tblSuggestions", "[OpenClosed] = 'Closed' and Group = 'Management' and [tblSuggestions]![DateClosed] Between #" & Format(dtDateFrom, "mm/dd/yyyy") & "# and #" & Format(dtDateTo, "mm/dd/yyyy") & "#"), 0#)
Text294 = OpenManagement + ClosedManagement
Text296 = AClosedManagement
End Sub
Here I am applying a filter based on the dates in my 2 x text boxes i mentioned in 1st post (start & end dates)
So rather than typing in the dates in these boxes I want to be able to select a week number (eg. I select week 46 so [DateFrom]=15/11/2010 & [DateToo]=21/11/2010)
Regards
TGK
Thanks for your response Doug
However,
Don't really know what you mean.
Here is the code i'm currently using as a filter for the data within my table:
Sub Management()
Dim OpenManagement As Integer
Dim ClosedManagement As Integer
Dim AClosedManagement As Integer
Dim dtDateFrom As Date
Dim dtDateTo As Date
dtDateFrom = CDate([Forms]![SuggestionSummary]![DateFrom])
dtDateTo = CDate([Forms]![SuggestionSummary]![DateToo])
'sum of filtered criteria between dates selected on form
OpenManagement = Nz(DCount("[ID]", "tblSuggestions", "[OpenClosed] = 'Open' and Group = 'Management' and [tblSuggestions]![Date] Between #" & Format(dtDateFrom, "mm/dd/yyyy") & "# and #" & Format(dtDateTo, "mm/dd/yyyy") & "#"), 0#)
ClosedManagement = Nz(DCount("[ID]", "tblSuggestions", "[OpenClosed] = 'Closed' and Group = 'Management' and [tblSuggestions]![Date] Between #" & Format(dtDateFrom, "mm/dd/yyyy") & "# and #" & Format(dtDateTo, "mm/dd/yyyy") & "#"), 0#)
AClosedManagement = Nz(DCount("[ID]", "tblSuggestions", "[OpenClosed] = 'Closed' and Group = 'Management' and [tblSuggestions]![DateClosed] Between #" & Format(dtDateFrom, "mm/dd/yyyy") & "# and #" & Format(dtDateTo, "mm/dd/yyyy") & "#"), 0#)
Text294 = OpenManagement + ClosedManagement
Text296 = AClosedManagement
End Sub
Here I am applying a filter based on the dates in my 2 x text boxes i mentioned in 1st post (start & end dates)
So rather than typing in the dates in these boxes I want to be able to select a week number (eg. I select week 46 so [DateFrom]=15/11/2010 & [DateToo]=21/11/2010)
Regards
TGK
What I'm suggesting is that the combo box have three columns in it:
(Up to you whether or not you show all three columns or not: you control this through the ColumnWidths property)
In the AfterUpdate event of the combo box (let's call it cboWeeks), you'd have
(Note that the Column collection starts numbering at 0)
CODE
Week Start End
45 2010-10-31 2010-11-06
46 2010-11-07 2010-11-13
47 2010-11-14 2010-11-20
48 2010-11-21 2010-11-27
49 2010-11-28 2010-12-04
45 2010-10-31 2010-11-06
46 2010-11-07 2010-11-13
47 2010-11-14 2010-11-20
48 2010-11-21 2010-11-27
49 2010-11-28 2010-12-04
(Up to you whether or not you show all three columns or not: you control this through the ColumnWidths property)
In the AfterUpdate event of the combo box (let's call it cboWeeks), you'd have
CODE
Private Sub cboWeeks_AfterUpdate()
Me!dtFrom = Me!cboWeeks.Column(1)
Me!dtTo = Me!cboWeeks.Column(2)
End Sub
Me!dtFrom = Me!cboWeeks.Column(1)
Me!dtTo = Me!cboWeeks.Column(2)
End Sub
(Note that the Column collection starts numbering at 0)
How do you get the week start and end dates? Do you have manuelly enter them?
Also, could you do this for a two week period and do this if the week starts on a day other than Sunday, (you helped me get that working before Doug).
Thanks;
Larry the
Also, could you do this for a two week period and do this if the week starts on a day other than Sunday, (you helped me get that working before Doug).
Thanks;
Larry the
Thanks again Doug,
I see what you mean now, I'll give it a go.
With 2011 coming up I was looking for something more automatic rather than manually having to change the combo each year.
I have attatched a dbase someone has done doing exactly as I need but instead they filtered for the month, and I'm not too sure how to make it work for weeks?
Not sure if you can make sense of it.
Many Thanks
TGK
I see what you mean now, I'll give it a go.
With 2011 coming up I was looking for something more automatic rather than manually having to change the combo each year.
I have attatched a dbase someone has done doing exactly as I need but instead they filtered for the month, and I'm not too sure how to make it work for weeks?
Not sure if you can make sense of it.
Many Thanks
TGK
In my example, I hard-coded the entries. However, if you look at the third example in my Database Journal article Uses for Cartesian Products in MS Access, you should be able to use the following as the RowSource for the combo box:
SELECT DatePart("ww", WhatDate), WhatDate, WhatDate + 6
FROM qryExample3a_DaysOfTheYear
WHERE Weekday([WhatDate])=1
Doing it for a two week period instead would take a little more work (and I'm afraid I don't have time to figure it out at the moment, but I know it's doable). And hopefully the article gives enough details that you can figure out how to handle weeks starting on a different day.
SELECT DatePart("ww", WhatDate), WhatDate, WhatDate + 6
FROM qryExample3a_DaysOfTheYear
WHERE Weekday([WhatDate])=1
Doing it for a two week period instead would take a little more work (and I'm afraid I don't have time to figure it out at the moment, but I know it's doable). And hopefully the article gives enough details that you can figure out how to handle weeks starting on a different day.
Hi Doug,
Going with your 1st example, I have made the table
However the dates will not go into the boxes, the table containing the dates and week numbers has the dates as Date\Time (format-shortdate)
The form textboxes are formated also as shortdate.
If I switch to column 0 it works and the week number transfers, so i'm guessing it has something to do with format?
Thanks for your time Doug
Rest assured its goina be appriciated by us all
Regards
TGK
(edit - My Bad Doug, I never brought in the 2 date fields when i made the combo lol
)
Thanks a lot
Going with your 1st example, I have made the table
However the dates will not go into the boxes, the table containing the dates and week numbers has the dates as Date\Time (format-shortdate)
The form textboxes are formated also as shortdate.
If I switch to column 0 it works and the week number transfers, so i'm guessing it has something to do with format?
Thanks for your time Doug
Rest assured its goina be appriciated by us all
Regards
TGK
(edit - My Bad Doug, I never brought in the 2 date fields when i made the combo lol
)Thanks a lot
If you use the ISO numbering of weeks, you can use this function which takes a year and a week number as both the first and the last dates of a calendar year may belong to week number 52/53:
You can use it like this where txtWeek and txtYear are the two textboxes for the parameter input (txtYear may be given a default value of the current year):
Me!txtDateFirst = ISO_DateOfWeek(Me!txtYear, Me!txtWeek, vbMonday)
Me!txtDateLast = ISO_DateOfWeek(Me!txtYear, Me!txtWeek, vbFriday)
As for picking the week number from a combobox, that's a good idea which screams for a List function (no queries or prepopulated tables) - one of the gems of Access):
Look up the on-line help for how List functions work and how to apply. A little learning may be needed but you won't regret.
/gustav
CODE
Public Function ISO_DateOfWeek( _
ByVal intYear As Integer, _
ByVal bytWeek As Byte, _
Optional ByVal bytWeekday As Byte = vbMonday) _
As Date
' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes: Years less than 100 will be handled as
' two-digit years of our current year frame.
' Years less than zero returns a zero date.
' A weeknumber of zero returns the requested
' weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.
' The fourth of January is always included in
' the first week of year intYear.
Const cbytDayOfFirstWeek As Byte = 4
' Number of days in a week.
Const cbytDaysOfWeek As Byte = 7
' Month of January.
Const cbytJanuary As Byte = 1
Dim datDateOfFirstWeek As Date
Dim intISOMonday As Integer
Dim intISOWeekday As Integer
Dim intWeekdayOffset As Integer
' No specific error handling.
On Error Resume Next
If intYear > 0 Then
' Weekday of Monday.
intISOMonday = WeekDay(vbMonday, vbMonday)
' Date of fourth of January in year intYear.
datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
' Weekday of fourth of January in year intYear.
intISOWeekday = WeekDay(datDateOfFirstWeek, vbMonday)
' Calculate offset from Monday in first week of year intYear.
intWeekdayOffset = intISOMonday - intISOWeekday
' Weekday of requested weekday.
intISOWeekday = WeekDay(bytWeekday, vbMonday)
' Calculate offset from requested weekday in first week of year intYear.
intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
' Date of requested weekday in first week of year intYear.
datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
' Date of requested weekday in requested week of year intYear.
datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
End If
ISO_DateOfWeek = datDateOfFirstWeek
End Function
ByVal intYear As Integer, _
ByVal bytWeek As Byte, _
Optional ByVal bytWeekday As Byte = vbMonday) _
As Date
' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes: Years less than 100 will be handled as
' two-digit years of our current year frame.
' Years less than zero returns a zero date.
' A weeknumber of zero returns the requested
' weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.
' The fourth of January is always included in
' the first week of year intYear.
Const cbytDayOfFirstWeek As Byte = 4
' Number of days in a week.
Const cbytDaysOfWeek As Byte = 7
' Month of January.
Const cbytJanuary As Byte = 1
Dim datDateOfFirstWeek As Date
Dim intISOMonday As Integer
Dim intISOWeekday As Integer
Dim intWeekdayOffset As Integer
' No specific error handling.
On Error Resume Next
If intYear > 0 Then
' Weekday of Monday.
intISOMonday = WeekDay(vbMonday, vbMonday)
' Date of fourth of January in year intYear.
datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
' Weekday of fourth of January in year intYear.
intISOWeekday = WeekDay(datDateOfFirstWeek, vbMonday)
' Calculate offset from Monday in first week of year intYear.
intWeekdayOffset = intISOMonday - intISOWeekday
' Weekday of requested weekday.
intISOWeekday = WeekDay(bytWeekday, vbMonday)
' Calculate offset from requested weekday in first week of year intYear.
intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
' Date of requested weekday in first week of year intYear.
datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
' Date of requested weekday in requested week of year intYear.
datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
End If
ISO_DateOfWeek = datDateOfFirstWeek
End Function
You can use it like this where txtWeek and txtYear are the two textboxes for the parameter input (txtYear may be given a default value of the current year):
Me!txtDateFirst = ISO_DateOfWeek(Me!txtYear, Me!txtWeek, vbMonday)
Me!txtDateLast = ISO_DateOfWeek(Me!txtYear, Me!txtWeek, vbFriday)
As for picking the week number from a combobox, that's a good idea which screams for a List function (no queries or prepopulated tables) - one of the gems of Access):
CODE
Public Function ListWeeknumbers( _
ctl As Control, _
lngNum As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) As Variant
' Creates a list of week numbers of current year according to the ISO 8601:1988 standard.
' 2007-07-09. Gustav Brock, Cactus Data ApS, CPH
Select Case intCode
Case acLBInitialize ' Initialize.
ListWeeknumbers = True
Case acLBOpen ' Open.
ListWeeknumbers = Timer ' Unique number as a check.
Case acLBGetRowCount ' Count of rows. The highest weeknumber in the current year.
ListWeeknumbers = ISO_WeekCount(Date)
Case acLBGetColumnCount ' Count of colums.
ListWeeknumbers = 1
Case acLBGetColumnWidth ' Width of column.
ListWeeknumbers = -1 ' Use default width of column.
Case acLBGetValue ' Fetch data.
ListWeeknumbers = lngRow + 1 ' First row is 0.
Case acLBEnd ' Close.
' ' Nothing to do.
End Select
End Function
ctl As Control, _
lngNum As Long, _
lngRow As Long, _
lngCol As Long, _
intCode As Integer) As Variant
' Creates a list of week numbers of current year according to the ISO 8601:1988 standard.
' 2007-07-09. Gustav Brock, Cactus Data ApS, CPH
Select Case intCode
Case acLBInitialize ' Initialize.
ListWeeknumbers = True
Case acLBOpen ' Open.
ListWeeknumbers = Timer ' Unique number as a check.
Case acLBGetRowCount ' Count of rows. The highest weeknumber in the current year.
ListWeeknumbers = ISO_WeekCount(Date)
Case acLBGetColumnCount ' Count of colums.
ListWeeknumbers = 1
Case acLBGetColumnWidth ' Width of column.
ListWeeknumbers = -1 ' Use default width of column.
Case acLBGetValue ' Fetch data.
ListWeeknumbers = lngRow + 1 ' First row is 0.
Case acLBEnd ' Close.
' ' Nothing to do.
End Select
End Function
Look up the on-line help for how List functions work and how to apply. A little learning may be needed but you won't regret.
/gustav
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.