Query & calculated field

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Thanks for the help! I have three fields of consequence
[closedate][duedate]and a calculated field [pastdue].

Every record will have a date in [duedate] but obviously
not necessarily in the [closedate]

I want to query records which have closed or are still
open 1 to 15 days past [duedate]

ie
[closedate] [duedate] [pastdue]
1/17/04 1/15/04 2
(null) 1/15/04 10 (Assuming that today is 1-25)

when the query runs I want it to ask for a date range
[beginning] & [ending] and I want [duedate] to be between
them.

There are a lot of variables and I can't quite get it.
Any help is greatly appreciated. Thanks
 
Hi Steve,

In your query for the column that has [closedate] do the following

Expr1: IIf(IsNull([closedate]),Now(),[closedate])

Then in your column for [pastdue] it would be

Expr1 - [duedate]

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "Steve" <[email protected]>
| Sender: "Steve" <[email protected]>
| Subject: Query & calculated field
| Date: Tue, 9 Mar 2004 13:07:02 -0800
| Lines: 20
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcQGGngXdXkbTvXgSy20+U55fTWiAw==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:193030
| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.queries
|
| Thanks for the help! I have three fields of consequence
| [closedate][duedate]and a calculated field [pastdue].
|
| Every record will have a date in [duedate] but obviously
| not necessarily in the [closedate]
|
| I want to query records which have closed or are still
| open 1 to 15 days past [duedate]
|
| ie
| [closedate] [duedate] [pastdue]
| 1/17/04 1/15/04 2
| (null) 1/15/04 10 (Assuming that today is 1-25)
|
| when the query runs I want it to ask for a date range
| [beginning] & [ending] and I want [duedate] to be between
| them.
|
| There are a lot of variables and I can't quite get it.
| Any help is greatly appreciated. Thanks
|
 
Back
Top