Query "Expression Problem"

  • Thread starter Thread starter Richard C
  • Start date Start date
R

Richard C

I am creating a query, what I want to do is display only
dates within 30 days of the current date. I used the
following expression;

<=Date()+14

However for some reason all the dates were displayed.
Doeas anyone have an Idea what might cause this? I placed
that expression under Deadline Date within my query!!

Rich
 
This should display all records with a Deadline Date earlier than 2 weeks
from today (Sept 27). Your question doesn't match your expression in any
way. If you want Deadline Dates within 30 days before or after the current
date, try:
Between Date()-30 and Date() + 30
 
My Mistake, but the problem is still the same. I have been
messing with the expression and altered it to 14 days
rather than 30. Even with 30 placed in the code rather
than 14, the error still occurs. The query still displays
dates as many as two months back, it just doesnt make
sense!!!

Rich
 
Here is the SQL Code

SELECT Traffic_light_Test.Task_Name,
Traffic_light_Test.Date_Deadline,
Traffic_light_Test.Task_Outline,
Traffic_light_Test.Task_Complete
FROM Traffic_light_Test
WHERE (((Traffic_light_Test.Date_Deadline)<=Date()+14))
ORDER BY Traffic_light_Test.Date_Deadline;

Rich C
 
Dear Richard:

<=Date()+14

means dates before 14 days in the future. This places no "lower
limit" so dates could be two months back and satisfy that test. It
makes a lot of sense.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I thought I suggested:
Between Date()-30 and Date() + 30
I don't see anything you your sql that resembles my suggestion.
 
Ok so what do I have to do to get it to display only the
14 days before the deadline. I dont want any other dates
to be displayed, just these specific dates that have a
deadline in the next 2 weeks are wanted in this query.

Rich
 
Dear Richard:

To be very specific, do you want to display those whose deadline is
today, or beginning tomorrow? Are the deadlines always recorded date
only, or could they have a time recorded?

BETWEEN Date() + 1 and Date() + 14 would include 14 days beginning
tomorrow and ending in two weeks. If you have any deadlines recorded
with a time of day (other than 00:00:00) on the 14th day hence, this
would be excluded. This filters only to the first moment of the day
14 days hence, not the whole day. This is no problem if you record no
time of day in the deadline column. That makes the time 00:00:00.

This is nothing but a variation on what Duane told you, except that
the range of days is different. But then you told him, "within 30
days of the current date" which he took to be 30 days either way.
That's naturally quite different from "the next 2 weeks." Even that
could be ambiguous. Next week doesn't start till Sunday, so that
could mean something besides the next 14 days. And still there's the
question of whether you include today or not.

My point is that it isn't easy to "specify" what you want, and that
doing so is a science itself. If you say "between tomorrow and 14
days from today" or "between today and 14 days from today" there would
be less confusion, eh?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Ok my mistake again I will try to be more specific!

The plan is to have deadlines inputted into the system.
What I want to do is create query, that will remind the
user of those deadlines 2 weeks before the date set for
the dead line. The Date is made up of Days, Months and
Year, no time value, that is too specific.

I have created a query, what I want it to do is check the
Deadline date, then compare that value to the current
date. If the current date is within the 2 week period of
the dead line, then that task and deadline should be
displayed.

I hope that makes more sense?

Rich
 
I want to create a query that will display deadlines that
are 2 weeks or less away from the current. I have no Time
involved in the deadline date, just Days, Months and years.

What I want the query to do, is check the current date on
the computer, then compare that date to the Deadlines
within the database. Then if any deadlines are within 14
days of the current date, then It will display the query
to the screen. So it is always 2 weeks before the date of
the deadline and the deadline day is included!!!

I hope this is specific enough?

Rich
 
Dear Richard:

I think what I gave you does this, except I don't know whether you
want to include "today" or not. Drop the + 1 if you want to include
today.

Please let me know if this does it for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top