Year-To-Date Query or Report

[rwr]

Does anyone have information on where I can find or how to do a YTD
query or report?

Thanks,

Ron

 

[Allen Browne]

Here's a starting point:
Subquery basics: Year to date
at:
http://allenbrowne.com/subquery-01.html#YTD

 

[John W. Vinson]

Does anyone have information on where I can find or how to do a YTD
query or report?

Right here:

Assuming that you have a date field in the table (if you don't you're in real

= DateSerial(Year(Date()), 1, 1) AND < Date() + 1

Base a Report on this Query if you want a report.

John W. Vinson [MVP]

 

[Guest]

Ron,
If you want to total information for specific years, try this:
1) Create a blank query and add your desired table/query.
2) In the first column type the following in the Field row
RonsTgtYear: Year([RonsDateField])
Note: "RonsTgtYear" becomes the name of the calculated field so name it
whatever you want. Replace "RonsDateField" with the field from the added
table/query that contains your date information.
3) Insert the fields that you want to sum up.
4) Select View>Totals to make the "Total:" row appear.
5) In the Total row ensure "Group By" is associated with the column created
in step 2.
6) Again in the Total row ensure "Sum" is associated with the fields you
want to sum up.
7) If you want to see only the information for a specific year, type that
year in Criteria under the column created in step 2.
8) If you want to see all the information without summing it up. Uncheck
the check next to View>Totals.

Hope this works for you Ron,
Cliff

 

[onedaywhen]

Assuming that you have adatefield in the table (if you don't you're in real

Here's a rewrite, using temporal functionality (and easier to port to
other SQL products):

BETWEEN DATEADD('YYYY', DATEDIFF('YYYY', #1990-01-01 00:00:00#,
NOW()), #1990-01-01 00:00:00#)
AND DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, NOW()),
#1990-01-01 23:59:59#)

Jamie.

--

 

[John W. Vinson]

Here's a rewrite, using temporal functionality (and easier to port to
other SQL products):

BETWEEN DATEADD('YYYY', DATEDIFF('YYYY', #1990-01-01 00:00:00#,
NOW()), #1990-01-01 00:00:00#)
AND DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, NOW()),
#1990-01-01 23:59:59#)

Jamie.

Are DateAdd and DateDiff any more portable than DateSerial? (not criticising,
just asking)

John W. Vinson [MVP]

 

[rwr]

Does anyone have information on where I can find or how to do a YTD
query or report?

Thanks,

Ron

Thanks to all replies. This really helps.

Thanks,
Ron

 

[onedaywhen]

Are DateAdd and DateDiff any more portable than DateSerial? (not criticising,
just asking)

Good question!

Hard to say, to be honest. Unlike Access/Jet, some SQL implementations
have more than one temporal data type so it's hard to make direct
comparisons.

Porting DATETIME to SQL Server is good, as you'd expect, and it has
DATEDIFF and DATEADD expressions with the same number of arguments
(the first argument is a literal rather than a text literal e.g. D
rather than 'D').

I don't use Oracle any more but I know it has MONTHS_BETWEEN and
ADD_MONTHS, which can obviously be used for year intervals; for
smaller granules (days, hours, etc) mathematic operators are used (as
we often see in Access land) e.g. trunc(date2) - trunc(date1) =
interval in days.

The way I see it, any given SQL product will have date/time
constructors and interval constructors. I know that not all SQL
products store/expose date/time data as numeric and I've no idea
whether all products overload the mathematic operators for datetime
data consistently. I've used Intersystems Caché which IIRC stored date/
time values as 'string' data and has DATEDIFF and DATEADD consistent
with Access/Jet and SQL Server (probably to assist porting from SQL
Server).

Specifically for Jet's DATESERIAL() function, I can't think of a
direct equivalent in the a-four-mentioned SQL products with which I'm
most familiar e.g. in SQL Server one tends to use concatenated strings
that are then CAST to DATETIME to achieve the same ends.

Jamie.

--