Querying on Active & Deactivation Dates

[ClawGee]

Hi Everyone,



I want to create a query that runs on a quarterly basis for my clients. The client table includes the clients activation date and their deactivation date, if they have one. If I wanted to know how many clients were active between 1/1/2012 and 3/31/2012 how can I search both currently active clients (with a blank entry in their deactivation text box) and since deactivated clients that were active on 3/31/2012?

Any help is greatly appreciated. I am brand new to access and have done some google searches but none of the solutions I have found work for my specific situation.

Thank you!

[June7]

All in one output? If I understand, you want all clients that were active within the date range. So, all clients where deactivation field Is Null OR deactivation later than end date. Will there always be an activation date? Try:

SELECT * FROM tablename WHERE Deactivation Is Null OR Deactivation > end date;

Now how do you want to pass the end date criteria to the query? Are you using the query as the RecordSource for a form or report?

[ClawGee]

Thank you very much for your response.

Yes, you are correct, there will ALWAYS be an activation date.

Unfortunately, when I use

SELECT * FROM tablename WHERE Deactivation Is Null OR Deactivation > 3/31/2012

it appears the query doesn't pay attention to the year? It gives me a list of clients that were deactivated prior to 3/31/2012. I am trying to find clients that are active between two dates, for instance, 1/1/2012 through 3/31/2012. How do I do that?

All in one output? If I understand, you want all clients that were active within the date range. So, all clients where deactivation field Is Null OR deactivation later than end date. Will there always be an activation date? Try:

SELECT * FROM tablename WHERE Deactivation Is Null OR Deactivation > end date;

Now how do you want to pass the end date criteria to the query? Are you using the query as the RecordSource for a form or report?

[June7]

Define 'active'. Should it include members who activated prior to 1/1/2012 and were not deactivated before 3/31/2012? The query I suggest does that but I forgot to show the date delimiters. Try:

SELECT * FROM tablename WHERE Deactivation Is Null OR Deactivation > #3/31/2012#

[ClawGee]

Yes, "Active" clients would be those that have been are have been active outside of the quarterly dates. So a search between the dates 1/1/2012 - 3/31/2012 would include clients that have been active since the year 2000 for example and it would also include clients that are now deactivated (lets say deactivated in 5/2012) but were still active up through 3/31/2012.

I tried what you have suggested by now I receive an error message, " Data Type Mismatch in Criteria Expression"

I'm just amking a mess of this aren't i?

[ClawGee]

Oh! Now I'm not receiving the error BUT I am still receiving records of clients who were deactivated in 7/2011 :/

[June7]

Do you have data stored as mm/dd/yyyy in a date/time field or is it mm/yyyy in a text field?

[ClawGee]

Do you have data stored as mm/dd/yyyy in a date/time field or is it mm/yyyy in a text field?

It's really strange. I checked that too and the information in the both the Activated column and Deactivated columns are saved as Date/Time Data Types :/ so I don't know why the years are not being taken into account.

Hm, i think I fixed it. When I went back into Design View, it looked like this:

EXPR1: WHERE Deactived is EXPR 2: OR Deactivated

Null >= 3/31/2012

So I changed the Expression using the drop down menu to look like this:

Deactivated Deactivated

is Null >= 3/31/2012

Instead or returning ALL records, it returns only 74 records.

[June7]

Okay, that is very weird. Glad you figured it out.

Do you really want the = sign?

[ClawGee]

Well you are clearly far more knowledgeable than I. Is it better to remove the = sign?

Thank you so much for all of your help! This information is PRICELESS!

Okay, that is very weird. Glad you figured it out.

Do you really want the = sign?

[June7]

If you include the = it means you will return records where the deactivation was on that exact date. I expected you would want to exclude those.