How Do I Create A MONTH TO DATE and YEAR TO DATE IN A QUERY

[JOSE COTES]

Do you know how to create a Month To Date expression and Year to Date
expression in a query.... thanks

[Arvin Meyer]

Here's some SQL that will help you:

MTD
---
Select DateField, DateField From Table1
Where Month([DateField])=Month(Date())) AND (Year([DateField])=Year(Date());

YTD
---
Select DateField From Table1
Where Year([DateField])=Year(Date());
-----
Arvin Meyer
ons...@esinet.net

JOSE COTES wrote in message <6ur80j$8m4$1...@news1.bu.edu>...

[Jeffrey Turner]

Jose,

For criteria in your query, you need

#1/1/98# <= [Date_Field] and Date() >= [Date_Field]

Jeff Turner

[George Shears]

Maybe Something Like This?

SELECT DISTINCTROW
-Sum([TransactionAmount]*(Month([TransactionDate])=Month(Date()))) AS
MonthToDate, Sum(Transaction.TransactionAmount) AS YearToDate,
Transaction.AccountID
FROM Transaction
WHERE (((Year([TransactionDate]))=Year(Date())) AND
((Month([TransactionDate]))<=Month(Date())))
GROUP BY Transaction.AccountID

I used Year(Date()) and Month(Date()) to limit the query to the current
year and month. You could, instead, used Year and Month parameters
(i.e. in query by form) to get any year and month of interest.

The MonthToDate Formula relies on Access evaluating False as 0 and True
as -1. Hence Amount * (True) = - Amount while Amount * (False) =0
Thus Sum(Amount * (TransMonth = ThisMonth)) gives negative sum of only
those transactions in ThisMonth.

I tossed in the grouping on AccountID just to illustrate grouping these
sorts of things.

Good Luck.

[Jeffrey Turner]

Jose,

If you read all three of the options posted so far, you should
obviously use Arvin's over mine -- hardcoding this year's date and
this month's date into your query is obviously a poor choice. :)

Oops,

Jeff

"Arvin Meyer" <a...@m.com> wrote:

>Here's some SQL that will help you:

>MTD
>---
>Select DateField, DateField From Table1
>Where Month([DateField])=Month(Date())) AND (Year([DateField])=Year(Date());

>YTD
>---
>Select DateField From Table1
>Where Year([DateField])=Year(Date());
>-----
>Arvin Meyer
>ons...@esinet.net

>JOSE COTES wrote in message <6ur80j$8m4$1...@news1.bu.edu>...

[JOSE COTES]

What about If My Fiscal Starts and Ends on October

Arvin Meyer wrote in message <6urarm$sep$1...@esinet2.esinet.net>...

[Arvin Meyer]

Make it difficult will you :) Here is a function that will return your
fiscal year:

intFMonth = the First Month of the Fiscal Year
in your case 10 for October

Function FY(dtDateIn As Date, intFMonth As Integer) As String
' Arvin Meyer 9/27/1997
On Error Resume Next

Dim intMonth As Integer
Dim intYear As Integer
intMonth = Month(dtDateIn)
intYear = Year(dtDateIn)
If intMonth >= intFMonth Then intYear = intYear + 1
FY = str(intYear)

End Function

Now your MonthToDate should be the same, but the YearToDate would be:

Select DateField From Table1
Where FY([DateField],10)=FY(Date(),10);
-----
Arvin Meyer
ons...@esinet.net

JOSE COTES wrote in message <6uu1d1$k30$1...@news1.bu.edu>...