Update a Yes / No field

[Duncs]

I have a table with 4 fields:

fldAccount - Number
fldTransactionAmt - Number
fldLimitAmt - Number
fldInTolerance - Yes / No

What I want to do, is create a query that will set the fldInTolerance
to 'Yes' if the fldTransactionAmt is within a £20 limit, either up or
down, of the fldLimitAmt...otherwise, set it to 'No'.

So, for a fldLimitAmt value of 130.25, if the fldTransactionAmt is in
the range of 110.25 -> 150.25 inclusively, then it is in tolerance and
so fldInTolerance should be set to 'Yes', otherwise, set it to 'No'.

The SQL that I have for the query at the moment is:

UPDATE tblData SET tblData.fldInTolerance = No
WHERE (((tblData.fldTransactionAmt)>([tblData]![fldLimitAmt]+20) Or
(tblData.fldTransactionAmt)<([tblData]![fldLimitAmt]-20)));

However, it doesn't seem to be working. Can anyone help me, or am I
missing something really simple?

TIA

Duncs

 

[BruceM]

There are a few points here. If something doesn't work, be specific. We
can assume the table didn't update, but it is best to be clear.
How are you running the SQL? I'm not noticing anything except maybe you
could try 0 or False instead of No. Try setting a calculated field to be
sure you are getting the expected value:
PlusTwenty: [fldLimitAmt] + 20

More to the point, why and when are you updating the entire table? Since
InTolerance can be determined from other values you should calculate it on
the fly. As it stands you will need to update the table whenever a
TransactionAmt value changes.

I have a table with 4 fields:

fldAccount - Number
fldTransactionAmt - Number
fldLimitAmt - Number
fldInTolerance - Yes / No

What I want to do, is create a query that will set the fldInTolerance
to 'Yes' if the fldTransactionAmt is within a £20 limit, either up or
down, of the fldLimitAmt...otherwise, set it to 'No'.

So, for a fldLimitAmt value of 130.25, if the fldTransactionAmt is in
the range of 110.25 -> 150.25 inclusively, then it is in tolerance and
so fldInTolerance should be set to 'Yes', otherwise, set it to 'No'.

The SQL that I have for the query at the moment is:

UPDATE tblData SET tblData.fldInTolerance = No
WHERE (((tblData.fldTransactionAmt)>([tblData]![fldLimitAmt]+20) Or
(tblData.fldTransactionAmt)<([tblData]![fldLimitAmt]-20)));

However, it doesn't seem to be working. Can anyone help me, or am I
missing something really simple?

TIA

Duncs

 

[Brendan Reynolds]

I have a table with 4 fields:

fldAccount - Number
fldTransactionAmt - Number
fldLimitAmt - Number
fldInTolerance - Yes / No

What I want to do, is create a query that will set the fldInTolerance
to 'Yes' if the fldTransactionAmt is within a £20 limit, either up or
down, of the fldLimitAmt...otherwise, set it to 'No'.

So, for a fldLimitAmt value of 130.25, if the fldTransactionAmt is in
the range of 110.25 -> 150.25 inclusively, then it is in tolerance and
so fldInTolerance should be set to 'Yes', otherwise, set it to 'No'.

The SQL that I have for the query at the moment is:

UPDATE tblData SET tblData.fldInTolerance = No
WHERE (((tblData.fldTransactionAmt)>([tblData]![fldLimitAmt]+20) Or
(tblData.fldTransactionAmt)<([tblData]![fldLimitAmt]-20)));

However, it doesn't seem to be working. Can anyone help me, or am I
missing something really simple?

I haven't tested this, so be sure to try it out on a test copy of your data
before letting it anywhere near your production data ...

UPDATE tblData SET tblData.fldInTolerance = (Abs(tblData.fldTransactionAmt -
tblData.fldLimitAmount) <=20)

The Abs() function returns the absolute value of the expression, eliminating
the need to test for positive or negative values.

 

[John Spencer (MVP)]

Well, first of all, I would think you would want to set fldInTolerance to TRUE
or FALSE depending on the result of the test.

UPDATE tblData
SET tblData.fldInTolerance = [fldTransactionAmt]>[fldLimitAmt]+20 Or
[fldTransactionAmt]<[fldLimitAmt]-20)

Another way to do this would be to use an update query that looks like this
UPDATE tblData
SET fldInTolerance = Abs([fldTransactionAmt] - [fldLimitAmt]) < 20

More importantly, you should not bother to store the value at all. The value
of fldInTolerance is always dependent on the two values of fldTransactionAmt
and fldLimitAmt and as such should be calculated when needed.

Under your present scheme if you change either field and fail to change
fldInTolerance your data will be wrong. You can always use the expression
Abs([fldTransactionAmt] - [fldLimitAmt]) < 20


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County