By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,997 Members | 1,470 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,997 IT Pros & Developers. It's quick & easy.

need help writing some code in an access query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
"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]






Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.