Help with an update query using an if statement

  • Thread starter Thread starter Jon M.
  • Start date Start date
J

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.
 
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
'====================================================
 
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?
 
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.


John Spencer said:
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.
Click to expand...
Click to expand...
 
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.
 
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.
 
You are a very good man, a very, very good man. Thanks!
--
Jon M.


KARL DEWEY said:
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


Jon M. said:
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.
Click to expand...
Click to expand...
 
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
'====================================================
 
Back
Top