Help with an update query using an if statement

[Jon M.]

Hi potential lifesavers, I am trying to do an update query using an if
statement and unfortunately I do not know as much about them as I had
thought. I have a field named Fraud, which is a check box. Based on whether
Fraud is true or false I want one of two fields to be updated. The update
occurring is a substraction from a class credits field. Basically this:
CreditsNeeded FraudNeeded Fraud(Checkbox) Class Credits
5 2 True
2
In this case my fraudneeded would be updated and now equal 0
If Fraud were False, then Creditsneeded would be updated and now equal 3. I
can't get my IIf statement to work in the criteria, and I tried putting Fraud
in the query twice but it won't run with the duplicate field. As always I
appreciate any help I can get.

 

[John Spencer]

Not quite clear what you want to do. I think you want to do the following.

UPDATE YourTable
SET FraudNeeded = IIF(Fraud,0,FraudNeeded)
, [Class Credits] = IIF(Fraud,[Class Credits],[Class Credits]+1

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the
way you expect.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

Try explaining it again for me. It seems like you want to update regardless
of whether Fraud is checked or not.
What gets subtracted from what if checked?
What gets subtracted from what if not checked?

 

[Jon M.]

What I'm going for is if Fraud is true then update Fraudneeded to equal
Fraudneeded-ClassCredits, if Fraud is false then update CreditsNeeded to
equal Creditsneeded-ClassCredits.
--
Jon M.

Not quite clear what you want to do. I think you want to do the following.

UPDATE YourTable
SET FraudNeeded = IIF(Fraud,0,FraudNeeded)
, [Class Credits] = IIF(Fraud,[Class Credits],[Class Credits]+1

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the
way you expect.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi potential lifesavers, I am trying to do an update query using an if
statement and unfortunately I do not know as much about them as I had
thought. I have a field named Fraud, which is a check box. Based on whether
Fraud is true or false I want one of two fields to be updated. The update
occurring is a substraction from a class credits field. Basically this:
CreditsNeeded FraudNeeded Fraud(Checkbox) Class Credits
5 2 True
2
In this case my fraudneeded would be updated and now equal 0
If Fraud were False, then Creditsneeded would be updated and now equal 3. I
can't get my IIf statement to work in the criteria, and I tried putting Fraud
in the query twice but it won't run with the duplicate field. As always I
appreciate any help I can get.

 

[Jon M.]

I'm looking to update CreditsNeeded or FraudNeeded. So if Fraud = true,
we're updating Fraudneeded to equal Fraudneeded-ClassCredits(the amount of
credits the class is worth). If Fraud = false, we're updating CreditsNeeded
to equal CreditsNeed-ClassCredits. Basically I have a db that keeps track of
classes for licensed insurance producers, they have to complete so many fraud
and nonfraud classes before their expiration date to keep their license. So
if a class is worth 3 credits, and fraud is checked off it will subtract 3
from the amount of Fraud credits needed. If Fraud is not checked off it will
subtract 3 from the nonfraud credits needed.

 

[KARL DEWEY]

This will update the field to the subtracted amount or to the current value ---

Fraudneeded --- IIF([Fraud] = 0, [Fraudneeded]-[ClassCredits], [Fraudneeded])

CreditsNeeded -- IIF([Fraud] = -1, [CreditsNeeded]-[ClassCredits],
[CreditsNeeded])

You will have bad data if the update is run twice or more. You probably
need to use a macro to do this update and in the same macro run a second
update that zeros out the ClassCredits field so nothing will be change if run
twice.

 

[Jon M.]

You are a very good man, a very, very good man. Thanks!
--
Jon M.

This will update the field to the subtracted amount or to the current value ---

Fraudneeded --- IIF([Fraud] = 0, [Fraudneeded]-[ClassCredits], [Fraudneeded])

CreditsNeeded -- IIF([Fraud] = -1, [CreditsNeeded]-[ClassCredits],
[CreditsNeeded])

You will have bad data if the update is run twice or more. You probably
need to use a macro to do this update and in the same macro run a second
update that zeros out the ClassCredits field so nothing will be change if run
twice.

--
KARL DEWEY
Build a little - Test a little

I'm looking to update CreditsNeeded or FraudNeeded. So if Fraud = true,
we're updating Fraudneeded to equal Fraudneeded-ClassCredits(the amount of
credits the class is worth). If Fraud = false, we're updating CreditsNeeded
to equal CreditsNeed-ClassCredits. Basically I have a db that keeps track of
classes for licensed insurance producers, they have to complete so many fraud
and nonfraud classes before their expiration date to keep their license. So
if a class is worth 3 credits, and fraud is checked off it will subtract 3
from the amount of Fraud credits needed. If Fraud is not checked off it will
subtract 3 from the nonfraud credits needed.

 

[John Spencer]

UPDATE YourTable
SET FraudNeeded = IIF(Fraud,Fraudneeded-[Classcredits],FraudNeeded)
, [CreditsNeeded] =
IIF(Fraud,[CreditsNeeded],[CreditsNeeded]-[ClassCredits])

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================