Date Filter Query

[VaYooper]

Hello,

Was wondering if someone could help with a date filter query I'm trying to add to a subform.

The selections I want are 'Within the past year' , 'Greater than a year' , and 'Never' What each of these selections would do is give me records that have a login date within the past year of today's date, greater than a year from today's date, and no record of today's date. Thank you for whatever assistance you can provide.

 

[VaYooper]

Sorry... meant to say

login date within the past year of today's date, greater than a year from today's date, and no record of any date.

 

[HiTechCoach]

To calculate the data a year from the current date use this function:

DateAdd ( interval, number, date )

To get the date one year ago from the current data use:

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

Example where Date() = 03/15/2015:

? DateAdd("yyyy",-1,Date())
3/15/2014

'Within the past year'

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

'Greater than a year'

< DateAdd("yyyy",-1,Date())

'Never'

Don't know what you mean by this. Please explain in more detail

 

[VaYooper]

Sorry... by 'Never' I mean that they do not have a last login date so the date is blank or null.

 

[VaYooper]

For the functions that you have provided. Where would I put these? Somewhere in the combo box properties?

 

[HiTechCoach]

For the functions that you have provided. Where would I put these? Somewhere in the combo box properties?

I am only guessing that combo box you are referring to has the selection list of 'Within the past year' , 'Greater than a year' , and 'Never' .

The criteria examples I gave you would be for a date filter criteria for the query you are adding to your sub form.

the way I would handle this is to use the After Update Event of the Combo box to build the SQL for the record source of the sub form.

 

[HiTechCoach]

Sorry... by 'Never' I mean that they do not have a last login date so the date is blank or null.

I am still confused. I think there must be more details to what you are trying to do that would be helpful.

 

[VaYooper]

I think I see where the confusion is. Let me start over.... In a table I have columns Last Name, First Name, Department, Access_Type, Login Date. This table has all personnel that have access to a particular program we have and it shows the last time that they logged into this particular program. So the dates in the login date column of my table can be from yesterday, 1 year ago, 4 years ago etc... but the login date can also be blank because they never logged into the program. So in this form I want the combobox to have three options: 'Within the past year' , 'Greater than a year' , and 'Never'. So when I click on 'Within the past year' this filters the data to only include records with a login date < 1 year or login date > 1 year by choosing 'Greater than a year' or login date is blank if they choose 'Never'. Hopefully I explained my situation better this time, thank you.

 

[HiTechCoach]

Use the after update event of the combo box to apply the desired

I will assume the field is named LogonDate. You may need to change it to you actual field name


'Within the past year'

[LogonDate] >= DateAdd("yyyy",-1,Date())

'Greater than a year'

[LogonDate] < DateAdd("yyyy",-1,Date())

'Never'

IsNull([LogonDate])