Filtering query by dates

[mlcampeau]

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:

SELECT Employee.ServiceDate, DateDiff("d",[ServiceDate],Now()) AS TotalServiceDays, Fix(DateDiff("d",[ServiceDate],Now())/365) AS ServiceYears, Fix((((DateDiff("d",[ServiceDate],Now())/365)-[ServiceYears])*12)) AS ServiceMonths, Round((((((DateDiff("d",[ServiceDate],Now())/365)-[ServiceYears])*12)) -[ServiceMonths])*30.4583) AS ServiceDays, IIf([AnniversaryDate]<>"NA",DateSerial(Year(AnniversaryDate),1,1),"NA") AS SupplementalEligibility, Switch(DateAdd("m",60,[ServiceDate])>Now(),DateAdd("m",60,[ServiceDate]), DateAdd("m",120,[ServiceDate])>Now(),DateAdd("m",120,[ServiceDate]), DateAdd("m",180,[ServiceDate])>Now(),DateAdd("m",180,[ServiceDate]), DateAdd("m",240,[ServiceDate])>Now(),DateAdd("m",240,[ServiceDate]), DateAdd("m",300,[ServiceDate])>Now(),DateAdd("m",300,[ServiceDate]), DateAdd("m",360,[ServiceDate])>Now(),DateAdd("m",360,[ServiceDate]), DateAdd("m",420,[ServiceDate])>Now(),DateAdd("m",420,[ServiceDate]), DateAdd("m",480,[ServiceDate])>Now(),DateAdd("m",480,[ServiceDate]),True,"NA") AS AnniversaryDate, Category.Code, CategoryReason.Desc, IIf([HireDate]<>[ServiceDate],"Flag","") AS Flag
FROM ......
WHERE (((EmplStatus.StatusCode)="A" Or (EmplStatus.StatusCode)="LOA") AND ((CategoryReason.Desc)="Permanent") AND ((EmplCategory.Active)=True) AND ((EmplJob.Active)=True) AND ((EmplOrganization.Active)=True) AND ((EmplStatus.Active)=True));

Any help would be appreciated!
I should mention that I have tried using the DatePart function which gives me just the year, but when I try to filter it with <=2008 or <=[Enter a Year] it says it's a data type mismatch.

[Scott Price]

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:

WHERE [TableName].[DateFieldName] = DatePart("mm/dd", "July 30") & "/" & Forms![FormName]![TextBoxName]

This is just off the top of my head tonight, but I'll give it a test in the morning to see if there are any wrinkles that need ironing :-)

Regards,
Scott

[mlcampeau]

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:

WHERE [TableName].[DateFieldName] = DatePart("mm/dd", "July 30") & "/" & Forms![FormName]![TextBoxName]

This is just off the top of my head tonight, but I'll give it a test in the morning to see if there are any wrinkles that need ironing :-)

Regards,
Scott

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).

[Scott Price]

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).

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

[mlcampeau]

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

That's exactly what I'm looking for, Scott, except it doesn't work. My date field is [AnniversaryDate], which is an expression in the same query. The Year([AnniversaryDate]) works great - it gives me the correct year, but as soon as I try to add criteria, (<=[Please Enter Year]), it also asks me for the value of AnniversaryDate.
If I try replacing [AnniversaryDate] with the expression, it works until I try to add criteria - then it says the expression is typed incorrectly, or it is too complex to be evaluated.

Year((Switch(DateAdd("m",60,[ServiceDate])>Now(),DateAdd("m",60,[ServiceDate]),DateAdd("m",120,[ServiceDate])>Now(),DateAdd("m",120,[ServiceDate]),DateAdd("m",180,[ServiceDate])>Now(),DateAdd("m",180,[ServiceDate]),DateAdd("m",240,[ServiceDate])>Now(),DateAdd("m",240,[ServiceDate]),DateAdd("m",300,[ServiceDate])>Now(),DateAdd("m",300,[ServiceDate]),DateAdd("m",360,[ServiceDate])>Now(),DateAdd("m",360,[ServiceDate]),DateAdd("m",420,[ServiceDate])>Now(),DateAdd("m",420,[ServiceDate]),DateAdd("m",480,[ServiceDate])>Now(),DateAdd("m",480,[ServiceDate]),True,"NA"))) AS VacationYear

I haven't made the parameter an integer because I'm not really sure how to do that...would that make a difference?

[mlcampeau]

I haven't made the parameter an integer because I'm not really sure how to do that...would that make a difference?

Okay, I searched how to declare the parameter as an integer and it hasn't helped at all....

[Scott Price]

WHERE (((Year([OrderEventDate]))<=[Please Enter Year]));

This is exactly the way I have it in my query that is working...

The Parameter [Please Enter Year] probably doesn't have to be declared as an Integer...

Regards,
Scott

[mlcampeau]

WHERE (((Year([OrderEventDate]))<=[Please Enter Year]));

This is exactly the way I have it in my query that is working...

The Parameter [Please Enter Year] probably doesn't have to be declared as an Integer...

Regards,
Scott

Scott,
Your logic does work. I tested it on my ServiceDate field that is stored in a table. The problem is that I'm trying to filter this based on a date that I am calculating. I am trying to use:
WHERE (((Year([AnniversaryDate]))<=[Please Enter Year]))
but since Anniversary Date is actually this:

Switch(DateAdd("m",60,[ServiceDate])>Now(),DateAdd("m",60,[ServiceDate]),DateAdd("m",120,[ServiceDate])>Now(),DateAdd("m",120,[ServiceDate]),DateAdd("m",180,[ServiceDate])>Now(),DateAdd("m",180,[ServiceDate]),DateAdd("m",240,[ServiceDate])>Now(),DateAdd("m",240,[ServiceDate]),DateAdd("m",300,[ServiceDate])>Now(),DateAdd("m",300,[ServiceDate]),DateAdd("m",360,[ServiceDate])>Now(),DateAdd("m",360,[ServiceDate]),DateAdd("m",420,[ServiceDate])>Now(),DateAdd("m",420,[ServiceDate]),DateAdd("m",480,[ServiceDate])>Now(),DateAdd("m",480,[ServiceDate]),True,"NA")

I'm having troubles. I think I mentioned before that if I put Year([AnniversaryDate]), it gives me the correct year, it's just when I try to add the filter that the problems arise. Any ideas??

[FishVal]

Hi, mlcampeau.

Not sure whether this will help.
Try to build a second query to filter the records returned by your query.

SELECT * FROM [YourQuery] WHERE [AnniversaryDate] < [Please Enter Year];
 

If this doesn't help, then try to put "Switch logic" to a public VBA function and use it in your query instead of Year(Switch(...)) expression

Public Function GetAnniversaryYear(ByVal dteServiceDate As Date) As Variant
 
    Dim i As Integer
 
    For i = 60 To 480 Step 60
        If DateAdd("m", i, dteServiceDate) > Date Then
            GetAnniversaryYear = Year(DateAdd("m", i, dteServiceDate))
            Exit Function
        End If
    Next i
 
    GetAnniversaryYear = Null
 
End Function
 
 

[mlcampeau]

Hi, mlcampeau.

Not sure whether this will help.
Try to build a second query to filter the records returned by your query.

SELECT * FROM [YourQuery] WHERE [AnniversaryDate] < [Please Enter Year];
 

If this doesn't help, then try to put "Switch logic" to a public VBA function and use it in your query instead of Year(Switch(...)) expression

Public Function GetAnniversaryYear(ByVal dteServiceDate As Date) As Variant
 
    Dim i As Integer
 
    For i = 60 To 480 Step 60
        If DateAdd("m", i, dteServiceDate) > Date Then
            GetAnniversaryYear = Year(DateAdd("m", i, dteServiceDate))
            Exit Function
        End If
    Next i
 
    GetAnniversaryYear = Null
 
End Function
 
 

I've tried creating the second query. Seems like it should work, but it doesn't. One thing I'm noticing is that if I sort the results by AnniversaryDate, it doesn't sort it by date, but rather, by the digits. For example it sorts like this:
1/1/2009
1/10/2009
1/11/2009
1/13/2012
1/15/2010
1/15/2012
1/18/2008
1/2/2011
1/22/2008
1/23/2010
etc....
I assume it's due to the format of the field. I'm not sure how to declare the format in a query, except for in the design view, right click on the grid, choose properties. But for the AnniversaryDate field, when I click the drop down box under Format, there are no options. I tried just typing in Short Date, but that doesn't do anything. Anyway, I'm not sure if that would have anything to do with why I'm having problems filtering this.

As for trying to add the public function, I'm really new to VBA...how would I refer to that function in my query?

[FishVal]

• Open VBA window.
• Main Menu > Insert > Module.
• Paste the code.
• Save the module.
• Use the function in your query as a regular VBA function you use
  e.g.
  

  SELECT ......, GetAnniversaryYear([ServiceDate]) AS AnniversaryYear FROM ...
   

The function returns anniversary year.
If you want to get anniversary date, then use this slightly modified code.

Public Function GetAnniversaryDate(ByVal dteServiceDate As Date) As Variant
 
    Dim i As Integer
 
    For i = 60 To 480 Step 60
        If DateAdd("m", i, dteServiceDate) > Date Then
            GetAnniversaryDate = DateAdd("m", i, dteServiceDate)
            Exit Function
        End If
    Next i
 
    GetAnniversaryDate = Null
 
End Function
 

[mlcampeau]

• Open VBA window.
• Main Menu > Insert > Module.
• Paste the code.
• Save the module.
• Use the function in your query as a regular VBA function you use
  e.g.
  

  SELECT ......, GetAnniversaryYear([ServiceDate]) AS AnniversaryYear FROM ...
   

The function returns anniversary year.
If you want to get anniversary date, then use this slightly modified code.

Public Function GetAnniversaryDate(ByVal dteServiceDate As Date) As Variant
 
    Dim i As Integer
 
    For i = 60 To 480 Step 60
        If DateAdd("m", i, dteServiceDate) > Date Then
            GetAnniversaryDate = DateAdd("m", i, dteServiceDate)
            Exit Function
        End If
    Next i
 
    GetAnniversaryDate = Null
 
End Function
 

I created the module and saved it as GetAnniversaryYear (I'm using the code that just gives the year). When I tried adding
SELECT ......, GetAnniversaryYear([ServiceDate]) AS AnniversaryYear
I get an error saying that GetAnniversaryYear is an undefined function... Any thoughts?

[mlcampeau]

Putting this:

WHERE ....(((Switch(DateAdd("m",60,[ServiceDate])>Now(),DateAdd("m",60,[ServiceDate]),DateAdd("m",120,[ServiceDate])>Now(),DateAdd("m",120,[ServiceDate]),DateAdd("m",180,[ServiceDate])>Now(),DateAdd("m",180,[ServiceDate]),DateAdd("m",240,[ServiceDate])>Now(),DateAdd("m",240,[ServiceDate]),DateAdd("m",300,[ServiceDate])>Now(),DateAdd("m",300,[ServiceDate]),DateAdd("m",360,[ServiceDate])>Now(),DateAdd("m",360,[ServiceDate]),DateAdd("m",420,[ServiceDate])>Now(),DateAdd("m",420,[ServiceDate]),DateAdd("m",480,[ServiceDate])>Now(),DateAdd("m",480,[ServiceDate]),True,"NA")))<="12/31" & "/" & [Enter the Year]))

filters it, but not the way I want. As I mentioned in a previous post, Access is not reading this field as a Date. So by me saying <="12/31" & "/" & [Enter the Year], and then I type 2008 in the prompt, it is giving me all results where the month starts with a 1 (i.e. 1/1/2008, 10/20/2010, 11/13/2009, 12/30/2011, etc...) So is filtering by month, day, then year. Somehow I need Access to realize that it is a date field so that it sorts properly. If I can get that to happen, the above criteria would work....

[Scott Price]

Putting this:

WHERE ....(((Switch(DateAdd("m",60,[ServiceDate])>Now(),DateAdd("m",60,[ServiceDate]),DateAdd("m",120,[ServiceDate])>Now(),DateAdd("m",120,[ServiceDate]),DateAdd("m",180,[ServiceDate])>Now(),DateAdd("m",180,[ServiceDate]),DateAdd("m",240,[ServiceDate])>Now(),DateAdd("m",240,[ServiceDate]),DateAdd("m",300,[ServiceDate])>Now(),DateAdd("m",300,[ServiceDate]),DateAdd("m",360,[ServiceDate])>Now(),DateAdd("m",360,[ServiceDate]),DateAdd("m",420,[ServiceDate])>Now(),DateAdd("m",420,[ServiceDate]),DateAdd("m",480,[ServiceDate])>Now(),DateAdd("m",480,[ServiceDate]),True,"NA")))<="12/31" & "/" & [Enter the Year]))

filters it, but not the way I want. As I mentioned in a previous post, Access is not reading this field as a Date. So by me saying <="12/31" & "/" & [Enter the Year], and then I type 2008 in the prompt, it is giving me all results where the month starts with a 1 (i.e. 1/1/2008, 10/20/2010, 11/13/2009, 12/30/2011, etc...) So is filtering by month, day, then year. Somehow I need Access to realize that it is a date field so that it sorts properly. If I can get that to happen, the above criteria would work....

What happens if you wrap the calculated date in CDate() function?

Regards,
Scott

[mlcampeau]

What happens if you wrap the calculated date in CDate() function?

Regards,
Scott

It comes up with "The expression is typed incorrectly or is too complex to evaluate." If I get rid of the criteria, it will display in the results, but if I try to sort it in the datasheet view, it says Datatype mismatch.

[FishVal]

No wonder it filters date as string as long as you use string criteria.
Try to change
"12/31" & "/" & [Enter the Year]
to
DateSerial([Enter the Year], 12, 31).
or
CDate("12/31" & "/" & [Enter the Year])
as Scott have suggested

Additionally:
"n/a" 's returned with Switch expression are expected cause "Type mismatch" error
Possible solutions:
1. Make Switch expression to return Null instead of "n/a"
2. VBA code where error can be trapped or prevented. Like this.

Public Function IsYearLessOrEqual(ByVal dteInput As Variant, _
                                  ByVal lngYear As Variant) As Boolean
 
    IsYearLessOrEqual = False
    On Error GoTo Exit_IsYearLessOrEqual
    If Year(dteInput) <= lngYear Then IsYearLessOrEqual = True
 
Exit_IsYearLessOrEqual:
 
End Function
 

[mlcampeau]

No wonder it filters date as string as long as you use string criteria.
Try to change
"12/31" & "/" & [Enter the Year]
to
DateSerial([Enter the Year], 12, 31).
or
CDate("12/31" & "/" & [Enter the Year])
as Scott have suggested

Additionally:
"n/a" 's returned with Switch expression are expected cause "Type mismatch" error
Possible solutions:
1. Make Switch expression to return Null instead of "n/a"
2. VBA code where error can be trapped or prevented. Like this.

Public Function IsYearLessOrEqual(ByVal dteInput As Variant, _
                                  ByVal lngYear As Variant) As Boolean
 
    IsYearLessOrEqual = False
    On Error GoTo Exit_IsYearLessOrEqual
    If Year(dteInput) <= lngYear Then IsYearLessOrEqual = True
 
Exit_IsYearLessOrEqual:
 
End Function
 

I knew it had to be something simple! I changed my criteria to:
DateSerial([Enter the Year], 12, 31) and that works great! Thanks so much, both of you, for your help!

[FishVal]

Happy coding.
And good luck in the rest of your project(s).

Best regards,
Fish

[Scott Price]

Glad you got it worked out!

Happy coding :-)

Regards,
Scott