Select only records past a key date

[Guest]

I need a query to identify records that have been opened longer than 60 days
from the received date, but excluding records that are closed (closed date is
Null). I have a statement in a form that displays a message if the key date
is past 60, but cannot work out how to get it into a query:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),[Date
Received])))>60,"Jeopardy",""

 

[Guest]

Hi, Johnny.

In query design view, enter the criteria for each on the same row, thus
creating a logical AND conjunction between the criteria.

Received Date: (Date()-[ReceivedDate])>60
Closed Date: Is Null

Or, in SQL,

SELECT fieldlist
FROM YourTable
WHERE (((Date()-[ReceivedDate])>60) AND ((YourTable.ClosedDate) Is Null));

Hope that helps.
Sprinks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your written explanation doesn't match your example IIf() expression.

The SQL translation of your written explanation is:

WHERE [Date Received] - Date() > 60
AND [Date Closed] IS NOT NULL

But that is not what your expression says.

Your inner IIF() function translates to:

If the Date Closed is NULL use the current (Date()), otherwise, use the
Date Received. Huh?!

Then the DateDiff() function figures out the number of days between the
Date Received and whichever date was chosen by the inner IIf() function.
If Date Closed is NOT NULL, then Date Received will be used. Then the
complete expression will evaluate to zero:

Date Received - Date Received = 0

I believe your complete expression should be:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),
[Date Closed])))>60,"Jeopardy","")

Which in SQL WHERE clause would be:

WHERE [Date Received] - Nz([Date Closed], Date()) > 60

The Nz() function "says" "If the first expression is NULL, use the
second expression." IOW, if Date Closed is null, use Date().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie6HoechKqOuFEgEQJ1XgCgscpooeHVOw/IjLpQ/YWcuk5w6xEAoMqH
oTSD9o2LXu+6Y4P77jqQJLiT
=brVi
-----END PGP SIGNATURE-----

Hi, Johnny.

In query design view, enter the criteria for each on the same row, thus
creating a logical AND conjunction between the criteria.

Received Date: (Date()-[ReceivedDate])>60
Closed Date: Is Null

Or, in SQL,

SELECT fieldlist
FROM YourTable
WHERE (((Date()-[ReceivedDate])>60) AND ((YourTable.ClosedDate) Is Null));

Hope that helps.
Sprinks

:

I need a query to identify records that have been opened longer than 60 days
from the received date, but excluding records that are closed (closed date is
Null). I have a statement in a form that displays a message if the key date
is past 60, but cannot work out how to get it into a query:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),[Date
Received])))>60,"Jeopardy",""

 

[Guest]

I had the same confusion re: the IIf statement, but as the written
explanation was clear, and it is such a common business task to look for old
unclosed records, I used it solely in providing the code. Johnny, if this
was not your intent, please more fully describe it.

Sprinks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your written explanation doesn't match your example IIf() expression.

The SQL translation of your written explanation is:

WHERE [Date Received] - Date() > 60
AND [Date Closed] IS NOT NULL

But that is not what your expression says.

Your inner IIF() function translates to:

If the Date Closed is NULL use the current (Date()), otherwise, use the
Date Received. Huh?!

Then the DateDiff() function figures out the number of days between the
Date Received and whichever date was chosen by the inner IIf() function.
If Date Closed is NOT NULL, then Date Received will be used. Then the
complete expression will evaluate to zero:

Date Received - Date Received = 0

I believe your complete expression should be:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),
[Date Closed])))>60,"Jeopardy","")

Which in SQL WHERE clause would be:

WHERE [Date Received] - Nz([Date Closed], Date()) > 60

The Nz() function "says" "If the first expression is NULL, use the
second expression." IOW, if Date Closed is null, use Date().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie6HoechKqOuFEgEQJ1XgCgscpooeHVOw/IjLpQ/YWcuk5w6xEAoMqH
oTSD9o2LXu+6Y4P77jqQJLiT
=brVi
-----END PGP SIGNATURE-----

Hi, Johnny.

In query design view, enter the criteria for each on the same row, thus
creating a logical AND conjunction between the criteria.

Received Date: (Date()-[ReceivedDate])>60
Closed Date: Is Null

Or, in SQL,

SELECT fieldlist
FROM YourTable
WHERE (((Date()-[ReceivedDate])>60) AND ((YourTable.ClosedDate) Is Null));

Hope that helps.
Sprinks

:

I need a query to identify records that have been opened longer than 60 days
from the received date, but excluding records that are closed (closed date is
Null). I have a statement in a form that displays a message if the key date
is past 60, but cannot work out how to get it into a query:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),[Date
Received])))>60,"Jeopardy",""

 

[Guest]

Sprinks,MGFoster. Thanks for your replies and you have maybe solved two
problems for me. I guess I never saw the wood for the trees in my need to
capture records in a query and got myself to deep in trying to use the
DiffDate function. The other answer should hopefully replace the one I am
using on my form to highlight a day counter of open records. I indeed
returned a value of 0 for some records, but this was the only way I was able
to also display the information I really wished to (I wasn't sure how to
provide a blank if the record was closed). I have two text boxes on my form,
one shows the amount of days of an unclosed record, and the other shows a
message that the record has been open for longer than 60 days"Jeopardy". That
is the one I put in this question, the other used is :
=DateDiff("d",[Date received],IIf(IsNull([Date closed])'date(),[Date
Received]))and indeed returns a 0 for a closed record.
You have to excuse my uselessness but I am a complete beginner and the help
menu was not much use in utilising DateDiff and IIf together.
Thanks again
Johnny

I had the same confusion re: the IIf statement, but as the written
explanation was clear, and it is such a common business task to look for old
unclosed records, I used it solely in providing the code. Johnny, if this
was not your intent, please more fully describe it.

Sprinks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your written explanation doesn't match your example IIf() expression.

The SQL translation of your written explanation is:

WHERE [Date Received] - Date() > 60
AND [Date Closed] IS NOT NULL

But that is not what your expression says.

Your inner IIF() function translates to:

If the Date Closed is NULL use the current (Date()), otherwise, use the
Date Received. Huh?!

Then the DateDiff() function figures out the number of days between the
Date Received and whichever date was chosen by the inner IIf() function.
If Date Closed is NOT NULL, then Date Received will be used. Then the
complete expression will evaluate to zero:

Date Received - Date Received = 0

I believe your complete expression should be:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),
[Date Closed])))>60,"Jeopardy","")

Which in SQL WHERE clause would be:

WHERE [Date Received] - Nz([Date Closed], Date()) > 60

The Nz() function "says" "If the first expression is NULL, use the
second expression." IOW, if Date Closed is null, use Date().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie6HoechKqOuFEgEQJ1XgCgscpooeHVOw/IjLpQ/YWcuk5w6xEAoMqH
oTSD9o2LXu+6Y4P77jqQJLiT
=brVi
-----END PGP SIGNATURE-----

Hi, Johnny.

In query design view, enter the criteria for each on the same row, thus
creating a logical AND conjunction between the criteria.

Received Date: (Date()-[ReceivedDate])>60
Closed Date: Is Null

Or, in SQL,

SELECT fieldlist
FROM YourTable
WHERE (((Date()-[ReceivedDate])>60) AND ((YourTable.ClosedDate) Is Null));

Hope that helps.
Sprinks

:


I need a query to identify records that have been opened longer than 60 days
from the received date, but excluding records that are closed (closed date is
Null). I have a statement in a form that displays a message if the key date
is past 60, but cannot work out how to get it into a query:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),[Date
Received])))>60,"Jeopardy",""

 

[Guest]

MGFoster

I had the same confusion re: the IIf statement, but as the written
explanation was clear, and it is such a common business task to look for old
unclosed records, I used it solely in providing the code. Johnny, if this
was not your intent, please more fully describe it.

Sprinks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your written explanation doesn't match your example IIf() expression.

The SQL translation of your written explanation is:

WHERE [Date Received] - Date() > 60
AND [Date Closed] IS NOT NULL

But that is not what your expression says.

Your inner IIF() function translates to:

If the Date Closed is NULL use the current (Date()), otherwise, use the
Date Received. Huh?!

Then the DateDiff() function figures out the number of days between the
Date Received and whichever date was chosen by the inner IIf() function.
If Date Closed is NOT NULL, then Date Received will be used. Then the
complete expression will evaluate to zero:

Date Received - Date Received = 0

I believe your complete expression should be:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),
[Date Closed])))>60,"Jeopardy","")

Which in SQL WHERE clause would be:

WHERE [Date Received] - Nz([Date Closed], Date()) > 60

The Nz() function "says" "If the first expression is NULL, use the
second expression." IOW, if Date Closed is null, use Date().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie6HoechKqOuFEgEQJ1XgCgscpooeHVOw/IjLpQ/YWcuk5w6xEAoMqH
oTSD9o2LXu+6Y4P77jqQJLiT
=brVi
-----END PGP SIGNATURE-----

Hi, Johnny.

In query design view, enter the criteria for each on the same row, thus
creating a logical AND conjunction between the criteria.

Received Date: (Date()-[ReceivedDate])>60
Closed Date: Is Null

Or, in SQL,

SELECT fieldlist
FROM YourTable
WHERE (((Date()-[ReceivedDate])>60) AND ((YourTable.ClosedDate) Is Null));

Hope that helps.
Sprinks

:


I need a query to identify records that have been opened longer than 60 days
from the received date, but excluding records that are closed (closed date is
Null). I have a statement in a form that displays a message if the key date
is past 60, but cannot work out how to get it into a query:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),[Date
Received])))>60,"Jeopardy",""

 

[Guest]

MGFoster...Tried your formula, but got an error message stating that an
expression has a function that has the wrong amount of arguments

I had the same confusion re: the IIf statement, but as the written
explanation was clear, and it is such a common business task to look for old
unclosed records, I used it solely in providing the code. Johnny, if this
was not your intent, please more fully describe it.

Sprinks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your written explanation doesn't match your example IIf() expression.

The SQL translation of your written explanation is:

WHERE [Date Received] - Date() > 60
AND [Date Closed] IS NOT NULL

But that is not what your expression says.

Your inner IIF() function translates to:

If the Date Closed is NULL use the current (Date()), otherwise, use the
Date Received. Huh?!

Then the DateDiff() function figures out the number of days between the
Date Received and whichever date was chosen by the inner IIf() function.
If Date Closed is NOT NULL, then Date Received will be used. Then the
complete expression will evaluate to zero:

Date Received - Date Received = 0

I believe your complete expression should be:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),
[Date Closed])))>60,"Jeopardy","")

Which in SQL WHERE clause would be:

WHERE [Date Received] - Nz([Date Closed], Date()) > 60

The Nz() function "says" "If the first expression is NULL, use the
second expression." IOW, if Date Closed is null, use Date().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQie6HoechKqOuFEgEQJ1XgCgscpooeHVOw/IjLpQ/YWcuk5w6xEAoMqH
oTSD9o2LXu+6Y4P77jqQJLiT
=brVi
-----END PGP SIGNATURE-----

Hi, Johnny.

In query design view, enter the criteria for each on the same row, thus
creating a logical AND conjunction between the criteria.

Received Date: (Date()-[ReceivedDate])>60
Closed Date: Is Null

Or, in SQL,

SELECT fieldlist
FROM YourTable
WHERE (((Date()-[ReceivedDate])>60) AND ((YourTable.ClosedDate) Is Null));

Hope that helps.
Sprinks

:


I need a query to identify records that have been opened longer than 60 days
from the received date, but excluding records that are closed (closed date is
Null). I have a statement in a form that displays a message if the key date
is past 60, but cannot work out how to get it into a query:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),[Date
Received])))>60,"Jeopardy",""

 

[Kari Mogensen via AccessMonster.com]

I need some help! And since you have been active on this message board
today, maybe you can help me out?

I am not that knowledgeable about Access. I am still learning! I know
enough to create a database. I have a database that I keep track of all of
my field reports for work. It has the county field,offender's name field,
ADC# field, Date Received field and Date Completed field.

I need a query for the dates completed that will select a range of dates
such as all of my January field reports. I have tried the > and < than
formulas and several others. I dont understand why i cant fix it. I cant
even find any information about it in my books or elsewhere on the web. How
do I select dates completed like from 1/1/2005 to 1/31/2005, which would
tell me all of my january completed reports????

PLEASE HELP ME : )

I need some serious help : )

Thanks so much!!!!!

Kari
(e-mail address removed)

 

[Guest]

Hi, Karl.

Believe me; we're all still learning and need help.

You can either use the AND conjunction with > and < or, more succinctly, use
the Between..And construction.

#1/1/2005# AND < #1/31/2005# OR

Between #1/1/2005 AND #1/31/2005#

If you're asking for the date inputs from a form, change the query criteria
to:

Between Forms!YourForm!BegDateControl AND Forms!YourForm!EndDateControl

Hope that helps.
Sprinks

 

[Guest]

Kari..It is best to make a new thread as other people can look at your
request. I am obviously a newbie but this may help:
Between [Start Date (mm/dd/yy)] And [End Date (mm/dd/yy)]
This should be put into the criteria field of your query (date field) and
when run will return a entry box for you to set start date and end date for
the information you need from your tables.

Johnny

PS I hope I never end up in your database...But then again I am from the
UK!!!!

 

[MGFoster]

MGFoster...Tried your formula, but got an error message stating that an
expression has a function that has the wrong amount of arguments

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here is the correct version:

IIf(DateDiff("d",[Date Received],IIf(IsNull([Date Closed]),date(),
[Date Closed])) > 60,"Jeopardy","")

I had one-too-many right parentheses before the "> 60."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQijXyoechKqOuFEgEQKaPgCgt9GAQ6wxmeZIhyLCe17TqW+eCCcAoIca
85wc8oSBjNcVw8KYhru+h4JX
=7Dkx
-----END PGP SIGNATURE-----