按日期过滤查询 [英] Filtering query by dates
问题描述
我有一个冗长的查询，我现在正在尝试过滤。该查询计算员工的周年日，在该日期中他们有资格获得下一级年假。 （即1 - 6年的服务，你有资格享受15天的假期等）我试图过滤报告，只显示其周年日期落入下一年的名字。员工有权在即将到来的周年纪念日的1月1日休假。 （即，如果个人的周年日期是2008年3月26日，他们有权享受2008年1月1日的休假）我的查询目前显示所有周年日期，其中包括2011年5月11日等日期。我说，我想过滤它，只显示那些周年纪念日在第二年内的人。因此，对于今年，我希望它显示所有周年日期< = 12/31/2008。如果我实际使用日期，我可以过滤报告，但我希望将来也能使用此报告，所以明年应该计算周年日期< = 12/31/2009。如果我可以做到这样，用户可以输入他们想要查看的年份，那将是理想的。因此，对于今年，如果有人想看看2009年的资格，他们可以。所以我要找的是这样的：
AnniversaryDate< = 12/31 / [输入年份]
用户输入年份。不确定这是否可行。这是我当前的sql减去SELECT语句中的一些无关的字段，减去FROM只是为了缩短代码：
I have a lengthy query that I am now trying to filter. The query calculates an employee''s Anniversary Date in which they are eligible for the next level of Annual Vacation. (i.e. For 1-6 years of service, you qualify for 15 days vacation, etc.) I am trying to filter the report to show only those names whose anniversary date falls into the following year. Employees are entitled to their vacation Jan.1 of their upcoming anniversary date. (i.e., if an individual''s anniversary date is March 26, 2008, they are entitled to their vacation Jan.1, 2008) My query currently shows all anniversary dates, which include dates such as May 11, 2011, etc. As I said, I want to filter it to show only those individuals whose anniversary date falls within the next year. So for this year, I want it to show all Anniversary Dates <= 12/31/2008. I can filter the report just fine if I actually use the date, but I want this report to be used in the future as well, so next year, it should be calculating Anniversary Dates <= 12/31/2009. It would be ideal if I could make it so that the user could input which year they would like to view. So for this year, if someone wanted to look at those eligibilities for 2009, they could. So what I''m looking for is something like this:
AnniversaryDate<=12/31/[Enter the Year]
where the user inputs the year. Not sure if this is possible. Here''s my current sql minus some fields in the SELECT statement that are irrelevant and minus the FROM just to shorten the code here:
推荐答案
您是否想过一个文本框来输入年份，然后将输入的年份与日期部分函数连接起来，该函数给出今天的日期和月份？
例如，日期字段中的where条件是这样的：
Have you thought of a text box to enter the year, then concatenating the entered year with a date part function that gives today''s day and month?
For example, a where criteria in your date field something like this:
您是否想过一个文本框来输入年份，然后将输入的年份与日期部分函数连接起来，该函数给出今天的日期和月份？
例如，日期字段中的条件是这样的：
Have you thought of a text box to enter the year, then concatenating the entered year with a date part function that gives today''s day and month?
For example, a where criteria in your date field something like this:
我没有这样做，因为我''我没有使用表格。这个数据库已经创建，我不得不做一些调整。我不应该说我根本没有使用表单...有一个导航栏显示报告名称列表，用户只需双击名称即可打开报告。没有添加文本框的空间，我真的不想乱用那个导航栏，因为它的主要功能是不要查看报告列表，而是浏览数据库。我真的希望我能以某种方式在查询本身中执行此操作，或者在On Open事件中使用VBA（尽管我的知识在VBA中非常有限）。
I haven''t done that because I''m not using a form. This database was already created and I am having to do some tweaking to it. I shouldn''t say I''m not using a form at all...there is a navigation bar that shows a list of the report names, and the user just double clicks the name to open the report. There is no room for adding textboxes and I really don''t want to mess around with that navigation bar as its main function isn''t to view the list of reports, but to navigate through the database. I''m really hoping that I can somehow do this in the query itself, or in VBA in the On Open event (My knowledge is very limited in VBA though).
好吧，在我的测试数据库中，我创建了一个名为[Please Enter Year]的参数，类型为整数，并将其放在新字段的Criteria网格中。在Field网格中我把它放在：VacationYear：Year（[YourDateField]）。
现在当我运行查询时它会弹出参数框，然后根据查询结果过滤我进入的那一年。
这是你要找的东西吗？
问候，
Scott
Well, in my test db, I created a parameter called [Please Enter Year] with type as integer and placed it in the Criteria grid of a new field. In the Field grid I put this: VacationYear: Year([YourDateField]).
Now when I run the query it pops up the parameter box, and then filters the query results according to the year I enter.
Is this something like you''re looking for?
Regards,
Scott
这篇关于按日期过滤查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
