Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Creating A 2 Plus/minus Range From A Given Number

Posted by: Lagerca Nov 13 2019, 07:11 PM

Good evening. I am trying to come up with a simple formula to create a Range from a given number.

Individuals begin our Holiday Season program with a Weigh In (lbs) field [WInNo]. At end of the 6 week program, the individual records his/her Weight (lbs) field [WOutNo]; I then calculate the difference as: DiffWOutWIn: [WOutNo]-[WInNo]

So, if I weigh in at 170 lbs and weigh out at 165 lbs; my result = -5 lbs, I lost pounds, no problem here.

Our goal though for this program is for the individuals to maintain their weight within a "plus or minus 2 lbs" during this 45 day period; this is not an exercise event to lose weight; but too maintain. If they maintain their weight "+/- 2 lbs", they are awarded 5 pts at the end of the program; there are other activities within the program to gain other points. If they gain more than 2 lbs or lose more than 2 lbs; no points are awarded. However; we also measure Body Fat % and there are points allotted here for reducing BF%

Back to the Q: What would be a good formula to use with this goal in mind to award them the 5 pts if they fall into a particular range of their "Weigh In" event, using the three fields? Thank You kindly... Curtis.

Posted by: MadPiet Nov 13 2019, 07:33 PM


Posted by: Lagerca Nov 14 2019, 07:34 PM

Good evening MadPiet. Thank you; your suggestion worked; just a brief summary how it worked for others.

1. I used the formula “PM2lbs: Abs([DiffWOutWin]) <=2” which gave me only a 0 or a -1 for each record. To grant the 5 points for those maintaining the Plus/Minus 2lbs, I used “PM2lbsPts: IIf([PM2lbs] = -1, 5, 0)”

2. Then for those maintaining the Plus/Minu 2% Body Fat goal, I used your same suggestion. In building the formula, the initial difference between Beg and End BF%; I used “DiffWOPWIP: [WOPerc]-WIPerc]” which gave me either a plus or minus value. To isolate the Plus/Minus 2% Body Fat records, I used “PM2PerBF: Abs([DiffWOPWIP] <=2” and then to award the 5 points for those maintaining that goal, “PM2BFPts: IIf([PM2PerBF] = -1,5,0)”. Finally awarding 10 points for those folks that had a greater than 2% in Body Fat loss; I used “G2PerBFPts: IIf([DiffWOPWIP] <-2, 10,0)”

Though I learned something. I had Body Fat % figures in my table formatted as Field Size: Single; Format as Percent, Decimal Places as 0. When I ran the formula above “PM2PerBF: Abs([DiffWOPWIP] <=2” the results for all records was a -1; no distinction to isolate the records. So, I changed the format in the table to a “Long Integer”; figuring I can display % format in my reports rather than the tables.

Thank you much, MadPiet for your suggestion. Curtis.

Posted by: Lagerca Dec 5 2019, 03:31 PM

Good afternoon MadPiet. As a follow-up: The “Abs” formula works fine when I have two values: [WInNo] and [WOutNo], (No is a Number). I have though encountered an issue in my design where the individual did not Weigh In [WInNo] in the beginning of our program or may not Weigh Out [WOutNo] at the end. Five points are awarded [PM2lbsPts] if they remain within the plus/minus 2lbs of the Weigh In. Other points are awarded for other events in our program.

To isolate those records where either the Weigh In/Out have no values, I created [WIOND], no data. I have trouble though using the “Abs” formula for those records that have both values. I hope this is clear! Please see the SQL below.... thank you kindly. Curtis.

SELECT tblTeamInfo.TeamID, tblTeamInfo.TeamName, tblMemberInfo.IndID, tblMemberInfo.NameLF, tblMemberInfo.WInNo,


IIf([WInNo]=0 Or [WOutNo]=0,1,0) AS WIOND,

[WOutNo]-[WInNo] AS DiffWOutWIn,

Abs([DiffWOutWIn])<=2 AS PM2lbs,

IIf([PM2lbs]=-1,5,0) AS PM2lbsPts,

Posted by: ADezii Dec 5 2019, 03:55 PM

  1. Sample Data (whole Numbers for weight):
    Person 1140142
    Person 2173172
    Person 3137141
    Person 4190192
    Person 5187184
    Person 6220225
    Person 7242241
    Person 8183179
    Person 9200203
    Person 10166164
    Person 11194213
    Person 12215216
  2. Query Definition:
    SELECT tblData.Person, tblData.WInNo, tblData.WOutNo, IIf(Abs([WInNo]-[WOutNo])<=2,5,Null) AS Points
    FROM tblData;
  3. Query OUTPUT:
    Person 11401425
    Person 21731725
    Person 3137141
    Person 41901925
    Person 5187184
    Person 6220225
    Person 72422415
    Person 8183179
    Person 9200203
    Person 101661645
    Person 11194213
    Person 122152165

Posted by: Lagerca Dec 6 2019, 09:44 AM

Good morning. Thank you... Almost! Your coding does work when we have both values for [WInNo] and [WOutNo]. It also works if we have one of the two values missing; 0 points awarded. However, if both fields have 0 values in each of them, we need to award them 0 points. Currently, when both values are missing, we still come up with 5 pts being awarded. Curtis.

Posted by: ADezii Dec 6 2019, 09:56 AM

See if the following works for you. ( 21.82K ): 2

Posted by: Lagerca Dec 6 2019, 10:18 AM

Good morning again. Since I am currently in my office, I can't download zip files; I will need to await my home; I will try that... However, I did find a temporary solution: using your abs code for [WIOPts]. Then using the isolated records [WIOND], if they are true, give those records 0 points, otherwise use the [WIOPts] points for [WtPts]. This seems to work.... Curtis.

SELECT tblTeamInfo.TeamID, tblTeamInfo.TeamName, tblMemberInfo.IndID, tblMemberInfo.NameLF, tblMemberInfo.WInNo, tblMemberInfo.WOutNo, IIf(Abs([WInNo]-[WOutNo])<=2,5,0) AS WIOPts, IIf([WInNo]=0 Or [WOutNo]=0,1,0) AS WIOND, IIf([WIOND]=1,0,[WIOPts]) AS WtPts

Posted by: Lagerca Dec 8 2019, 08:30 PM

Yes, thank you ADezii. I looked at the zipped file and your coding: Points: IIf(Abs([WInNo]-[WOutNo])<=2,5,0) works, even if both values are empty, that is, there are no 0's. In establishing the design of the db, 0 is the default. If the default of data entries is 0, your formula "will grant" 5 points. However, if the data entry is empty, no 0's, then yes the coding works. Thank You. Curtis.