Calculations with Zero values

[Guest]

I am in a pinch for a solution to a calculation that uses a zero for the
default value in the fields that do not have a 1, 2, 3, 4, 5 or 6 in them.

This is the sql code:
SELECT tblClassEntries.PlaceJ1, (7-[placeJ1]) AS Points,
tblClassEntries.PlaceJ2, (7-[PlaceJ2]) AS PointsJ2, tblClassEntries.[Back#],
tblClassEntries.[Class#], tblClassEntries.RiderName, tblHorse.HorseName,
tblClassEntries.RiderAge
FROM tblClassEntries LEFT JOIN tblHorse ON tblClassEntries.[Back#] =
tblHorse.[Back#]
WHERE (((tblClassEntries.PlaceJ1)>=1)) OR (((tblClassEntries.PlaceJ2)>=1));

I didn't think about it when I developed all of this that the calculation of
(7-[placeJ1]) would actually return 7 where I would like to have a 0.

My problem is that with the default value of 0 in the field, I cannot figure
out how to return the 0 if the place is not 1, 2, 3, 4, 5, 6.

The PlaceJ1 and PlaceJ2 fields are numeric, double integer, general number
with default value set to 0.

Would someone please suggest a calculation where if the value of PlaceJ1 or
PlaceJ2 is zero, that the Points or PointsJ2 value returns a 0?

Thanks in advance for your help. If more info is required, please let me
know.
DN

 

[John Spencer]

One method would be to use Mod 7 on the calculated result.

Another would be to return 0 if placeJ1 is zero otherwise run the
calculation.

(7-[placeJ1]) MOD 7 AS Points

Or

IIF([PlaceJ1]=0,0,7-[placeJ1]) AS Points

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

 

[Guest]

Thanks for your suggestions John. I did try the (7-[placeJ1]) MOD 7 AS
Points and it seems to be working great. I have my work cut out for me
tonight as I review 2 other formulas to make sure they are calculating
correctly. This can be sooo confusing!
Thanks again,
DN

One method would be to use Mod 7 on the calculated result.

Another would be to return 0 if placeJ1 is zero otherwise run the
calculation.

(7-[placeJ1]) MOD 7 AS Points

Or

IIF([PlaceJ1]=0,0,7-[placeJ1]) AS Points

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

I am in a pinch for a solution to a calculation that uses a zero for the
default value in the fields that do not have a 1, 2, 3, 4, 5 or 6 in them.

This is the sql code:
SELECT tblClassEntries.PlaceJ1, (7-[placeJ1]) AS Points,
tblClassEntries.PlaceJ2, (7-[PlaceJ2]) AS PointsJ2,
tblClassEntries.[Back#],
tblClassEntries.[Class#], tblClassEntries.RiderName, tblHorse.HorseName,
tblClassEntries.RiderAge
FROM tblClassEntries LEFT JOIN tblHorse ON tblClassEntries.[Back#] =
tblHorse.[Back#]
WHERE (((tblClassEntries.PlaceJ1)>=1)) OR
(((tblClassEntries.PlaceJ2)>=1));

I didn't think about it when I developed all of this that the calculation
of
(7-[placeJ1]) would actually return 7 where I would like to have a 0.

My problem is that with the default value of 0 in the field, I cannot
figure
out how to return the 0 if the place is not 1, 2, 3, 4, 5, 6.

The PlaceJ1 and PlaceJ2 fields are numeric, double integer, general number
with default value set to 0.

Would someone please suggest a calculation where if the value of PlaceJ1
or
PlaceJ2 is zero, that the Points or PointsJ2 value returns a 0?

Thanks in advance for your help. If more info is required, please let me
know.
DN