Query of only Records Due to be Installed in 5 Days

  • Thread starter cw via AccessMonster.com
  • Start date
C

cw via AccessMonster.com

I know this is simple, but can't figure it out?

- I have a field called: InstallDate
- Need a query to return just the records that are due to be installed in 5
days.

I tried the following on InstallDate in the criteria: >Date()-5
But it gives me records for: 06/02/2008, 06/26/2008 and 06/30/2008?

What should I use?
Thanks,
cw
 
B

Bob Barrows [MVP]

cw said:
I know this is simple, but can't figure it out?

- I have a field called: InstallDate
Click to expand...

Is it a Date/Time field?
- Need a query to return just the records that are due to be
installed in 5 days.

I tried the following on InstallDate in the criteria: >Date()-5
But it gives me records for: 06/02/2008, 06/26/2008 and 06/30/2008?
Click to expand...
What is the result of Date()-5? Answer this to yourself. Are the dates for
those records greater than the result of Date()-5? I would suspect so.

Perhaps this is the criterion you are looking for?
 
C

cw via AccessMonster.com

Yes, It is Date/Time.
Is it a Date/Time field?


What is the result of Date()-5? Answer this to yourself. Are the dates for
those records greater than the result of Date()-5? I would suspect so.

Perhaps this is the criterion you are looking for?
Click to expand...
 
B

Bob Barrows [MVP]

So did you stop reading at that point? I believe I gave you the answer in
the rest of my reply ...
 
C

cw via AccessMonster.com

Sorry about that..
When I use: >Date() + 5 I get:
records 06/26/2008 and 06/30/2008?

If I'm thinking correctly on this, I should not get any records, because
there are no InstallDates pending that are 5 days or less.

In my database, I have a routine to auto-send a plain text email saying:
"The following Orders are due to be installed this week: bla, bla, bla.."

My macro will run everyday, and not send the email if there are no Installs
due this week.

Thanks,
cw

So did you stop reading at that point? I believe I gave you the answer in
the rest of my reply ...
Yes, It is Date/Time.
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
B

Bob Barrows [MVP]

I don't really understand your requirement. Maybe you want records where
Installdate is less than 5 days ago?
Sorry about that..
When I use: >Date() + 5 I get:
records 06/26/2008 and 06/30/2008?

If I'm thinking correctly on this, I should not get any records,
because there are no InstallDates pending that are 5 days or less.

In my database, I have a routine to auto-send a plain text email
saying: "The following Orders are due to be installed this week: bla,
bla, bla.."

My macro will run everyday, and not send the email if there are no
Installs due this week.

Thanks,
cw

So did you stop reading at that point? I believe I gave you the
answer in the rest of my reply ...
Yes, It is Date/Time.
Click to expand...
[quoted text clipped - 17 lines]
Date() + 5
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Maybe you want <Date() + 5
If this is not it then post a list of dates and indicate which ones should
be returned like this --
5/22/2008
5/23/2008
5/24/2008
5/25/2008
5/26/2008
5/27/2008
5/28/2008 X
5/29/2008 X
5/30/2008 X
5/31/2008 X
6/1/2008 X
6/2/2008 X
6/3/2008
6/4/2008
6/5/2008
6/6/2008
6/7/2008
6/8/2008
6/9/2008
6/10/2008
6/11/2008

--
KARL DEWEY
Build a little - Test a little


cw via AccessMonster.com said:
Sorry about that..
When I use: >Date() + 5 I get:
records 06/26/2008 and 06/30/2008?

If I'm thinking correctly on this, I should not get any records, because
there are no InstallDates pending that are 5 days or less.

In my database, I have a routine to auto-send a plain text email saying:
"The following Orders are due to be installed this week: bla, bla, bla.."

My macro will run everyday, and not send the email if there are no Installs
due this week.

Thanks,
cw

So did you stop reading at that point? I believe I gave you the answer in
the rest of my reply ...
Yes, It is Date/Time.
Click to expand...
[quoted text clipped - 17 lines]
Date() + 5
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try

WHERE InstallDate Between Date() and Date()+5


Criteria would be
Field: YourFieldName
Criteria: Between Date() and Date()+5

That should return all records where the date is beween June 6 and June 11
(today is June 6). If your field has a time associated with it you will get
all records up to and including June 11 at midnight, but no records on June
11th after midnight.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
C

cw via AccessMonster.com

Guys, I really appreciate all the help & input.

Looks like John's idea was the golden ticket.

I ended up creating a new field in the query grid:
UpcomingT1s: [InstallDate]-Date()
with Criteria: Between 0 and 7

This now shows me just those Orders that have Install Dates next week.

On the auto-email, I always create a Report based on my query, which prints
to PDF and sent out via a Macro via Windows Scheduler.
Question: What script or code do you guys use to send out the query data in
the Body of the email instead of an actual PDF attachment?

Thanks,
cw
 
Top