need help writing some code in an access query

[Paul Mendez]

I really need some help

Date Code ConCAT Bal_Fwd NS_Fees Amt_Coll Cur_End_Bal
1/15/2004 KW 11KW2003 $500.00 $250.00 $250.00
2/15/2004 KW 12KW2003 $300.00 $500.00 -$200.00
3/15/2004 KW 1KW2004 ???? $123.00 $584.00 -$461.00
4/15/2001 KW 2KW2004 XXXX $223.00 $484.00 -$261.00
1/15/2004 SO 11O2003 $300.00 $250.00 $50.00
2/15/2004 SO 12SO2003 $300.00 $500.00 -$200.00
3/15/2004 SO 1SO2004 ---- $123.00 $584.00 -$461.00

This is what I have in my query showing.
What I need help doing is: the spot where the ????, ---- and XXXX are
in the Bal_Fwd column, I need the value showing in the Cur_End_Bal
column, but is has to be from two months before. Always two months
prior. So... the ???? should show $250.00 and the XXXX should show
-$200.00 and the ---- should show $50.00. (The reason that the other
bal_fwd are blank is cause there are no records 2 months previous.)
What kind of code should I use in the query? The query name is
"qryAR" and the Cur_End_Bal is calculated (NS_Fees - Amt_Coll).
What should I do, I am stuck????
THank you
Paul Mendez

[WindAndWaves]

It looks like you need to use the following formulae:

iif([bal_fwd]='????', dlookup("[bal_fwd]", "[tbln]", "[tbln]![Date]=#" &
dateadd("m", -2, [date]) & "#", [bal_fwd])

please read the help on
iif function
dlookup function
dateadd function

idea is if ???? then lookup value where date = date - 2 months...
"Paul Mendez" <pm*****@thelyndco.com> wrote in message
news:99**************************@posting.google.c om...

I really need some help

Date Code ConCAT Bal_Fwd NS_Fees Amt_Coll Cur_End_Bal
1/15/2004 KW 11KW2003 $500.00 $250.00 $250.00
2/15/2004 KW 12KW2003 $300.00 $500.00 -$200.00
3/15/2004 KW 1KW2004 ???? $123.00 $584.00 -$461.00
4/15/2001 KW 2KW2004 XXXX $223.00 $484.00 -$261.00
1/15/2004 SO 11O2003 $300.00 $250.00 $50.00
2/15/2004 SO 12SO2003 $300.00 $500.00 -$200.00
3/15/2004 SO 1SO2004 ---- $123.00 $584.00 -$461.00

This is what I have in my query showing.
What I need help doing is: the spot where the ????, ---- and XXXX are
in the Bal_Fwd column, I need the value showing in the Cur_End_Bal
column, but is has to be from two months before. Always two months
prior. So... the ???? should show $250.00 and the XXXX should show
-$200.00 and the ---- should show $50.00. (The reason that the other
bal_fwd are blank is cause there are no records 2 months previous.)
What kind of code should I use in the query? The query name is
"qryAR" and the Cur_End_Bal is calculated (NS_Fees - Amt_Coll).
What should I do, I am stuck????
THank you
Paul Mendez

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.687 / Virus Database: 448 - Release Date: 16/05/2004

[John Winterbottom]

"Paul Mendez" <pm*****@thelyndco.com> wrote in message
news:99**************************@posting.google.c om...

I really need some help

Date Code ConCAT Bal_Fwd NS_Fees Amt_Coll Cur_End_Bal
1/15/2004 KW 11KW2003 $500.00 $250.00 $250.00
2/15/2004 KW 12KW2003 $300.00 $500.00 -$200.00
3/15/2004 KW 1KW2004 ???? $123.00 $584.00 -$461.00
4/15/2001 KW 2KW2004 XXXX $223.00 $484.00 -$261.00
1/15/2004 SO 11O2003 $300.00 $250.00 $50.00
2/15/2004 SO 12SO2003 $300.00 $500.00 -$200.00
3/15/2004 SO 1SO2004 ---- $123.00 $584.00 -$461.00

This is what I have in my query showing.
What I need help doing is: the spot where the ????, ---- and XXXX are
in the Bal_Fwd column, I need the value showing in the Cur_End_Bal
column, but is has to be from two months before. Always two months
prior. So... the ???? should show $250.00 and the XXXX should show
-$200.00 and the ---- should show $50.00. (The reason that the other
bal_fwd are blank is cause there are no records 2 months previous.)
What kind of code should I use in the query? The query name is
"qryAR" and the Cur_End_Bal is calculated (NS_Fees - Amt_Coll).
What should I do, I am stuck????

select q.[Date], q.Code, q.ConCAT,
(
select q2.Cur_End_Bal
from qryAR as q2
where q2.Code = q.Code
and q2.[Date] = DateAdd("m", -2, q.[date]
) as Bal_Fwd,
q.NS_Fees, q.Amt_Coll, q.Cur_End_Bal
from qryAR as q
order by q.Code, q.[date]