Year-To-Date Query Help Needed

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

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.
--
Dave Hargis, Microsoft Access MVP


"Terry" wrote:

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

PERHAPS

SELECT strAccountID
, Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curCred it,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebi t,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" wrote in message
...
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

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


"John Spencer" wrote in message
...
PERHAPS

SELECT strAccountID
, Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curCred it,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebi t,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" wrote in message
...
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

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),curCred it,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebi t,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
..

"John Spencer" wrote in message
...
PERHAPS

SELECT strAccountID
, Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curCred it,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebi t,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" wrote in message
...
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

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


"John Spencer" wrote in message
...
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),curCred it,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebi t,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
.

"John Spencer" wrote in message
...
PERHAPS

SELECT strAccountID
, Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curCred it,0) -
Sum(IIF(dtmDate Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),curDebi t,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" wrote in message
...
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

On Mon, 11 Feb 2008 09:19:16 -0600, "Terry" wrote:

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("y yyy",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;