Please help with Date Subtraction

[Guest]

I need a query to give me the current year and current month minus one year,
so June 2006 to June 2005, etc. One field for the year in the query now says:
Field Column: Year: Format([Release Date:],"yyyy")
Total: Group By, Ascending
Criteria says: Format(Now(),"yyyy")

How do I do this and is it possible?

 

[Rick Brandt]

I need a query to give me the current year and current month minus
one year, so June 2006 to June 2005, etc. One field for the year in
the query now says: Field Column: Year: Format([Release
Date:],"yyyy")
Total: Group By, Ascending
Criteria says: Format(Now(),"yyyy")

How do I do this and is it possible?

WHERE [Release Date] >= DateSerial(Year(Date())-1, Month(Date()), 1)
AND [Release Date] < DateSerial(Year(Date()), Month(Date()) + 1, 1)

 

[Guest]

I copied your entire statement you gave me in the criteria field and it asks
me for a release date parameter value. If I don't enter one, I'll get no
results, if I enter 06/2006, I'll get all records dating back to 1999.
Should I change the Total field to "Where" or "Expression" instead of "Group
By"? Did you mean to say I should copy that whole statement in the criteria
field?

I need a query to give me the current year and current month minus
one year, so June 2006 to June 2005, etc. One field for the year in
the query now says: Field Column: Year: Format([Release
Date:],"yyyy")
Total: Group By, Ascending
Criteria says: Format(Now(),"yyyy")

How do I do this and is it possible?

WHERE [Release Date] >= DateSerial(Year(Date())-1, Month(Date()), 1)
AND [Release Date] < DateSerial(Year(Date()), Month(Date()) + 1, 1)

 

[Rick Brandt]

I copied your entire statement you gave me in the criteria field and
it asks me for a release date parameter value. If I don't enter one,
I'll get no results, if I enter 06/2006, I'll get all records dating
back to 1999. Should I change the Total field to "Where" or
"Expression" instead of "Group By"? Did you mean to say I should
copy that whole statement in the criteria field?

If your field name actually contains a colon then it is because I left that out.
Best practice for field names is no spaces and letters only.

 

[Guest]

Didn't work unfortunately. Someone else gave me the statement "Between
Now()-365 And Now()" and this doesn't produce any results in the datasheet
view. Any other ideas?

 

[Ron2006]

What is the datatype and format of the field that you are comparing
against.

Also you probably will want to use

Date-365 And Date

Now() automatically includes a time constraint. Also this method will
not account for leap year.

Ron.

 

[Guest]

In the original table, "Release Date:" field is a general date data type,
which of course would include day, month and year. For this query, they
separated it by using "Year: Format([Release Date:],"yyyy")" in the Field Row
description, nothing in the table row, and Group By and Ascending sorted. In
the criteria, it's:
Format(Now(),"yyyy"). I don't think any criteria should be placed in the
month columns (there are two), it should be placed in the year to go back one
year unless I'm wrong.

 

[Ron2006]

With what you have described it says that it will select all records
that have a year in the field "Release Date:" {with a colon - which is
unusual but apparently how they defined it} that is equal to 2006. An
acceptable format but is it what you want?

Is there any other criteria in any of the other columns dealing with
date?

Note:
I have never seen a field defined with a colon in the name. Something
to be avoided as you define your tables.

Ron

 

[Ron2006]

Another way to format the top and the bottom line for that matter is:

Top line: Year([Release Date:])

Bottom line: Year(Date())

To avoid confusion in my own mind, if I am trying to compare dates
(without time) I always use Date(). Unless you are supecific now() will
include the time automatically.

Ron

 

[Guest]

I know that colons should not be placed in filed names and unfornately, this
was defined by another user when they built this db. There are two other
columns dealing with date: 2 month columns One that says "M1:
Format([Release Date:],"mm")" - this lists what numbered month it is (Jan is
01, Feb is 02, etc.) and a month column that lists the month by name when
query is run: Month: Format([Release Date:],"mmmm"). So essentially the
Release Date from the table is in general date format for its data type and
in this query it is separated.
What I need is the pivot chart to go back one year from it's present month
and year. From June 2006 to June 2005, Aug 2006 to Aug 2005, etc. I just
figured that the year criteria column is all I needed to mess with.

My other problem is getting the pivot chart to translate to a MS graph
chart. The specific suppliers I've chosen in the pivot chart is not what's
coming out on the graph chart, it's showing them all. This must be something
in the row source in properties - a separate hurdle I'm battling.

 

[Ron2006]

I don't think that working with just the year will do it since you are
trying to go back 12 months not just get this year or last year.

One other question: you said the datatype for [Release Date:] was
timedate. What is the specific format? is it shortdate or blank(the
default)

I would suggest taking the criteria off of the year, (you may or may
not wish the year field to still be there depending on your graph.
What I would suggest is create a column with the [Release Date:] in the
top row and
the range/criteria be:

between dateadd("y",-1,date()) and date()


This will give you today's date and today's date less 1 year


I think everything else should be left alone, just to see if you are
getting the records you want.

Ron

 

[Rick Brandt]

Didn't work unfortunately. Someone else gave me the statement
"Between Now()-365 And Now()" and this doesn't produce any results in
the datasheet view. Any other ideas?

Please define "didn't work". You can see what is happening we cannot. I assure
that if you apply that criteria to a DateTime field it will do what you want.

 

[Guest]

I took out the other date fields, dragged the Release Date: field in and used
the expression you supplied, but it produced no data in the datasheet view.
Yes, the datatype for Release Date is general date/time which includes date
and time (the very top selection when you go into the bottom window of design
view and choose the format of date that is desired.

 

[Rick Brandt]

I took out the other date fields, dragged the Release Date: field in
and used the expression you supplied, but it produced no data in the
datasheet view. Yes, the datatype for Release Date is general
date/time which includes date and time (the very top selection when
you go into the bottom window of design view and choose the format of
date that is desired.

Post the entire SQL statement. Clearly the trees are obscuring the forest.