conditons for queries

  • Thread starter Thread starter nicole
  • Start date Start date
N

nicole

I have a query that i am trying to build where I wouldl
like for the query to find all dates 30 days before the
current date. How would i enter this into my condition
box? I have tried (name of box with date) = now ()-30 and
have had not luck any help would be greatly appreciated
 
Under the date field that you want to test in your query, put this in the
Criteria line:

(Date() - 30)

If you want all records that are 30 days or more in the past, put:

<=(Date() - 30)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I have a query that i am trying to build where I wouldl
like for the query to find all dates 30 days before the
current date. How would i enter this into my condition
box? I have tried (name of box with date) = now ()-30 and
have had not luck any help would be greatly appreciated
Click to expand...

Do you want to get just the records created on February 20? or all
records up to that date? Also, does the date field contain a time
portion? Now() is NOT equal to today's date - it's the current date
and time, accurate to a few microseconds, so searching for Now()-30
will probably return nothing unless you're very lucky!

If it's just a pure date field, use

DateAdd("d", -30, Date())

as a criterion. If it's got both date and time portions, use
= DateAdd("d", -30, Date()) AND < DateAdd("d", -29, Date())
Click to expand...

to get all values for that 24 hour timespan.
 
Dear Nicole:

I'm thinking you need to compare some column in your data to "30 days
before the current date." It doesn't make sense to compare the value
in some "box" to that, does it?

Now, do you want everything where the date is exactly 30 days ago, or
where it is within the last 30 days? It sounds like you want exactly
30 days ago.

Next, with the Now() function, you are dealing with, not a date, but a
date/time. Exactly 30 days ago doesn't mean any time on that date,
but exactly 30 days ago, down to a few milliseconds.

You could run a 24 hour period, from this time of day 30 days ago to
this time of day 31 days ago. But, more likely, you mean any time in
the midnight to midnight period of that day. In that case, the Date()
function is what you probably want.

Finally, there's a question of how your dates are stored in the table.
Are they all recorded with a zero time, or can they be recorded with
hours:minutes:seconds? If no time values are entered, then the time
will be recorded as 0, which will then begin matching the Date()
function values, and things will start to work.

I'll stop now to see if any of this is helping, and to see how you
answer my questions and check my assumptions.

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