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#];
Hamed NasrRetired IT ProfessionalCommented:
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)
awking00Information Technology SpecialistCommented:
I think that should be IIF([DR/CR]="Credit", Amount * (-1), Amount)
Also, you might add IIF([S/L] IN ("S","L"),

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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#];
awking00Information Technology SpecialistCommented:
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?
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),


pdebaetsCommented:
Try

IIF([DR/CR]="Credit", a.Amount * (-1), a.Amount) as Amount,
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
 
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.