How to modify a SQL Insert statement based on conditions of particular fields when using Access 2003?

I am developing an Access 2003 application using an MDB type file.

How would you modify the following SQL statement so that when records are INSERTED INTO
tblOpenItems,

1) if the value of the field [S/L] = "S" and the value of the field [DR/CR] ="Credit"
then the value of the field Amount = Amount * (-1)

2)  if the value of the field [S/L] = "L" and the value of the field [DR/CR] ="Credit"
then the value of the field Amount = Amount * (-1)

3)  if the value of the field [S/L] = "S" and the value of the field [DR/CR] ="Debit"
then the value of the field Amount = Amount

4)  if the value of the field [S/L] = "L" and the value of the field [DR/CR] ="Debit"
then the value of the field Amount = Amount
-------------------------------------------
INSERT INTO tblOpenItems
SELECT b.[Bank Code] AS Bank,
(IIf(IsNull(a.JournalRef),' ',
a.JournalRef)+','+IIf(IsNull(a.CTREF),' ',a.CTREF)+','+IIf(IsNull(a.AgentCTREF),' ',
a.AgentCTREF)+','+IIf(IsNull(a.OurREF),' ',a.OurREF)) AS refNo,
a.Mnem AS Type, IIf([S/L]="S",IIf([DR/CR]="Credit",
"A","B"),IIf([DR/CR]="Credit","D","C")) AS T, (IIf(IsNull(a.AgentComments),' ',
a.AgentComments)+','+IIf(IsNull(a.AgentReference),' ',a.AgentReference)) AS Description,
a.Amount AS Amount, ('H') AS manual
FROM tblBanks AS b, OI_Intell AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdebaetsCommented:
You can try the sql below, but you need to fill in the "????". What should amount be set to if none of the criteria are met?

INSERT INTO tblOpenItems
SELECT b.[Bank Code] AS Bank,
(IIf(IsNull(a.JournalRef),' ',
a.JournalRef)+','+IIf(IsNull(a.CTREF),' ',a.CTREF)+','+IIf(IsNull(a.AgentCTREF),' ',
a.AgentCTREF)+','+IIf(IsNull(a.OurREF),' ',a.OurREF)) AS refNo,
a.Mnem AS Type, IIf([S/L]="S",IIf([DR/CR]="Credit",
"A","B"),IIf([DR/CR]="Credit","D","C")) AS T, (IIf(IsNull(a.AgentComments),' ',
a.AgentComments)+','+IIf(IsNull(a.AgentReference),' ',a.AgentReference)) AS Description,

iif(([S/L] = "S" or [S/L] = "L")  and [DR/CR] ="Credit" , a.Amount * (-1), iif(([S/L] = "S" or [S/L] = "L")  and [DR/CR] ="Debit" , a.Amount, ????)) as Amount,

('H') AS manual
FROM tblBanks AS b, OI_Intell AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
hnasrCommented:
Try: If [S/L] = "S" ot "L" Then the code can be reduced to check for "Credit" or "Debit"

IIF([S/L]="Credit", Amount*-1, Amount)
0
awking00Commented:
I think that should be IIF([DR/CR]="Credit", Amount * (-1), Amount)
Also, you might add IIF([S/L] IN ("S","L"),
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zimmer9Author Commented:
Why do I get the following error:

No destination field name in INSERT INTO statement
(IIF([DR/CR]="Credit", Amount * (-1),Amount))

when executing the following SQL INSERT INTO Statement?

INSERT INTO tblOpenItems
SELECT b.[Bank Code] AS Bank, b.[COST CNTR] AS Office, (' ') AS checkNum, (IIf(IsNull(a.JournalRef),' ',a.JournalRef)+','+IIf(IsNull(a.CTREF),' ',a.CTREF)+','+IIf(IsNull(a.AgentCTREF),' ',a.AgentCTREF)+','+IIf(IsNull(a.OurREF),' ',a.OurREF)) AS refNo, Format(Format(a.ValueDate,"0000\/00\/00"),"mm\/dd\/yyyy") AS [trans date], a.Mnem AS Type, Format(Format([a.Posting Date],"0000\/00\/00"),"mm\/dd\/yyyy") AS [process date], (' ') AS Pending, a.Age AS AgeDays, IIf([S/L]="S",IIf([DR/CR]="Credit","A","B"),IIf([DR/CR]="Credit","D","C")) AS T, (IIf(IsNull(a.AgentComments),' ',a.AgentComments)+','+IIf(IsNull(a.AgentReference),' ',a.AgentReference)) AS Description,
IIF([DR/CR]="Credit", Amount * (-1), Amount),
(' ') AS UserID, (' ') AS AuditDate, Message AS Footnote, [b.Report Name] AS responsibility, b.rptID AS rptID, [b.Report Name] AS [Report Name], ('H') AS manual
FROM tblBanks AS b, OI_Intell AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
awking00Commented:
That's an entirely different set of attributes from your original insert statement. Can you describe the tblOpenItems table as to its attributes and datatypes?
0
zimmer9Author Commented:
My original statement was:

a.Amount AS Amount

so how would I replace this statement with

IIF([DR/CR]="Credit", Amount * (-1), Amount),


0
pdebaetsCommented:
Try

IIF([DR/CR]="Credit", a.Amount * (-1), a.Amount) as Amount,
0
zimmer9Author Commented:
tblOpenItems

Bank                Text
Office              Text
checkNum       Text
refNo               Text
trans date        Text
Type                Text
process date   Text
Pending           Text
AgeDays         Number
T                      Text
Description      Text
amount            Number
UserID             Text
AuditDate        Text
footnote          Text
responsibility   Text
rptID                Number
REPORT NAME Text
manual             Text
 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.