Promt user to enter year/week in complaint query (1 Viewer)

[KevinSlater]

Hi,

I have an access query that looks at a table named DATA-COMPLAINTS which has complaints data. One of the fields on the DATA-COMPLAINTS table is named: CC_ENTRY_DATE which is the date each complaint record was entered in the format mm/dd/yy

What I would like to be able to do is prompt the user to enter a year, and then prompt the user to enter a week number (from 1 to 53). The results would then show all complaints data that falls within the year/week they enter.

I have a linked SQL table named dbo_@CALENDER which has data about our accounting calendar such as quarters, periods, weeks, week start date, week end date and number of working days in the week. I was thinking I could make use of this table to compare the complaints entry date (CC_ENTRY_DATE) with the week/year the user enters. See attached screenshot of this table

I guess I need some SQL code to achieve? this but not sure where to start, any help would be great.

 

[DCrake]

Take a look at this link it may help you do what you want to do. See the documentation for a more clearer explanation of the logic.

 

[KevinSlater]

Hi David, thanks for your reply. I'm looking at the database and document in the link you provided but at the moment I cant think how i could do something similar to allow the user to enter a year and week rather than a date from and date to range

 

[DCrake]

If in your underlying query you had a column for Year and one for Week


In you standard module you would create 2 public variables

Public IntWeek As Integer
Public IntYear As Integer

Then create 2 functions

Public Function GetWeekNo() As Integer
   GetWeekNo = IntWeek
End Function

Public Function GetYearNo() As Integer
   GetYearNo = IntYear
End Function

Then in your filtering form you have 2 controls

TxtWeekNo

TxtYearNo

Then on the after update event of each control

IntWeek = Me.TxtWeekNo

IntYear = Me.TxtWeekNo


Finally in your query under the respective columns in the condition line

=GetWeekNo()

=GetYearNo()

Using this method you are not reliant on a particular form being open you can pass the value to the public variable from anywhere as the query is not reliant on picking the value from a forms control.

HTH

 

[KevinSlater]

In the query i dont currently have a column for year and week-this is in a seperate table named:dbo_@CALENDER and the screenshot is just from that table. I was just thiking of making use of the calender table to determine the correct year and week in comparison to the complaint entry date.

The following is a screenshot of the current query, which just has the DATA-COMPLAINTS table and a filed with CC_ENTRY_DATE in the format DD/MM/YY

 

[KevinSlater]

Do you mean add the calender table to the query and add the week and year fileds? would i need some way of linking these to the complaints table and the CC_ENTRY_DATE?

 

[DCrake]

So in your query create two more columns

FilterWeek:Format([CC_ENTRY_DATE],"ww")

FilterYear:Format([CC_ENTRY_DATE],"yyyy")

And place the GetWeekNo,GetYearNo under the columns.

 

[KevinSlater]

Ok thanks i added those addional colums and as a quick test before i proceed with creating the functions i filtered the crieteria of the weeks and years to test, but i just thought of an issue that might cause a problem using your suggested functions/code. For example if i enter 2010 for the year and 5 for the week the complaints results showing are not correct.

our accouting calendar for week 5 for example runs from 01/02/2010 to 07/02/2010
our week 1 is from 01/01/2010 to 10/01/10 (3 working days)
our week 52 is from 27/12/2010 to 31/12/10 (4 working days)

I know this looks not very logical but its how our companys accounting calendar is setup.
the first week in the year usually has less days and also the last week in the year.

 

[DCrake]

Then you need to make a small modification to the GetWeekNo function to adjust the number to get the correct period.

Or write a new function that gets the fiscal week number from the date and use this as the filter week number.

It's a case of horses for courses.

 

[KevinSlater]

I think a small calculation would likely not work as the way the weeks falls varies each year. I guess writting a function would be more reliable, or some how looking up the correct week on the calendar table.

 

[KevinSlater]

Hi,

I got this working by using the following code, by doing a non-equi join between the two tables: DATA-COMPLAINTS and dbo_@CALENDER

It seems to be working fine. Thanks for you help on this David.

Kevin

SELECT Data-Complaints.*
FROM Data-Complaints
INNER JOIN dbo_@Calendar
ON Data-Complaints.[CC_Entry_Date] >= dbo_@Calendar.U_StartDate
AND Data-Complaints.[CC_Entry_Date] <= dbo_@Calendar.U_EndDate
WHERE dbo_@Calendar.U_Year = [Year:]
AND dbo_@Calendar.U_WeekNum = [Week:]