Year-To-Date Query Help Needed

[Terry]

I don't know if this is a query question or a report question or both, but
I'm really confused.

I have a table (tblGL) with the following fields in it:
dtmDate = Date Field
strAccountId = Text Field
curDebit = Currency Field
curCredit = Currency Field

What I am trying to accomplish is to run a query in orfer to produce a
report that will print out the following information:

Account ID Current Month Y-T-D
1000 $100.00 $300.00
2000 0 $500.00

In other words, I want to pull a query to be used in a report to show three
columns. First column Account Number, Second Column, Current Month's Total
for this account, and third column the year to date amount for this account.

Any suggestions on how to accomplish this would be greatly appreciated.

Thanks,
Terry

 

[Klatuu]

SELECT dtmDate, strAccountId, curDebit, curCredit FROM tblGL WHERE
Year(dtmDate) = Year(Date()) AND dtmDate <= Date();

Will return only records for the current year that are up to and including
today's date.

 

[John Spencer]

PERHAPS

SELECT strAccountID
, Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curCredit,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebit,0) as CurMonth
, Sum(curCredit) - Sum(curDebit) as YTD
FROM tblGL
WHERE Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY strAccountID

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Terry]

I tried this, but I get a Syntax error that there is a missing operator in
query expression.

 

[John Spencer]

Well I did miss something in the where clause - what field should be in the
comparison.

I may have missed a parentheses or two also - although (obviously) I don't
see that I am.

SELECT strAccountID
, Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curCredit,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebit,0) as CurMonth
, Sum(curCredit) - Sum(curDebit) as YTD
FROM tblGL
WHERE dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)
GROUP BY strAccountID

Try this one. If it fails try removing items form the select clause to see
if one of them is faulty.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Terry]

John:

Thank you so much for trying to help me in this matter, but for the life of
me, I still can not get the query to work. Is there something else that I
might try.

Thanks,
Terry

 

[Michael Gramelspacher]

Thank you so much for trying to help me in this matter, but for the life of
me, I still can not get the query to work. Is there something else that I
might try.

Thanks,
Terry

PARAMETERS [Enter Date:] DateTime;
SELECT strAccountID,
SUM(IIF(DATEDIFF("m",DATEADD("m",DATEDIFF("m",0,[Enter Date:]),0),
dtmDate) = 0,curCredit - curDebit,
0)) AS MTD,
SUM(IIF(DATEDIFF("yyyy",DATEADD("yyyy",DATEDIFF("yyyy",0,[Enter
Date:]),0),
dtmDate) = 0,curCredit - curDebit,
0)) AS YTD
FROM tblGL
WHERE dtmDate IS NOT NULL
AND dtmDate < DATEADD("d",1,[Enter Date:])
GROUP BY strAccountID;