Transfer data from Unbound Textbox to Bound Textbox

[scoobz1234]

Good Afternoon!

I have been searching but am unable to find out how i could do this. i have an unbound text box that calculates values from three bound text boxes. i then need to save that calculated value, so i am trying to transfer the data from the unbound textbox to a bound textbox. can't seem to figure that out. my unbound text box's source is

 =Val([txtHip].[Value])+Val([txtWaist].[Value])-Val([txtNeck].[Value])

also i would like all these text boxes to be not visible and locked, so it would have to update automatically without click or onupdate or anything of that sort.

[rpeare]

Do not save the calculated value. There is no reason to do so. You can do the calculation at the run time of any query, report or form. Storing calculated values is a very bad idea.

[scoobz1234]

Useful things.zip

if i dont save this value, i can't see any way to do what i am ultimately trying to do... i use three values to calculate a circumference value, thats what i posted above. then with that value i need to use the query to cross a persons HEIGHT then their CIRCUMFERENCE value, and then from those values it will show their Body fat %

[rpeare]

Can you convert your database to 2003 (preferably) or 2007 if you have no other options I can't view 2010+


What you want to do is still possible strictly with queries.

[scoobz1234]

im currently using access 2007, my home access is 2010 but i am at work

[rpeare]

Ok. First I changed your sample data to actually get data that was in the range of your tblMale_Standars (by the way you can put both female and male standards in the same table, just put in an additional field with the gender so and create another link via gender in the queries below to make it work)

For person 1 I have HIP 15.6, Waist 20, Neck 15
For person 2 I have HIP 15.8, Waist 21, Neck 18

First I created this query:

[SELECT tblBCP.BCPID, tblBCP.HEIGHT, tblBCP.HIP, tblBCP.WAIST, tblBCP.NECK, [hip]+[waist]-[neck] AS Circum, Int([hip]+[waist]-[neck]) AS CircumCalc, IIf([circum]-[circumcalc]>0.25 And [circum]-[circumcalc]<0.75,0.5,IIf([circum]-[circumcalc]>=0.75,1,0)) AS Remainder, [circumcalc]+[remainder] AS Final, (Int([hip]+[waist]-[neck]))+(IIf(([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))>0.25 And ([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))<0.75,0.5,IIf(([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))>=0.75,1,0))) AS FinalFullCalc
FROM tblBCP;

I left a bunch of calculations in there to help you see how I put the final calculation together for the circumference you use to look up values in your male standards table.

CALL THIS QUERY qryCircum

Second, create this query:

SELECT QryCircum.BCPID, QryCircum.HEIGHT, QryCircum.HIP, QryCircum.WAIST, QryCircum.NECK, QryCircum.Circum, QryCircum.CircumCalc, QryCircum.Remainder, QryCircum.Final, QryCircum.FinalFullCalc, tlbMale_Standards.Body_Fat_Percentage
FROM QryCircum LEFT JOIN tlbMale_Standards ON (QryCircum.FinalFullCalc = tlbMale_Standards.Circumference_Value) AND (QryCircum.HEIGHT = tlbMale_Standards.HEIGHT);

You can call this query whatever you want.

you can see that person 1 and 2 now have a looked up BMI value where person 6 does not because there was no matching data in your male standards table.

[scoobz1234]

Thank you for all your help, i am having an issue with the second Query where it says Type Mismatch in Expression.

also, you shouldnt of had to change any sample data, that data is from real people who i just changed the pertinant information on, for males you only use neck and waist, so waist-neck=circumference and for females you do waist+hip-neck=circumference.

for instance MALE person 1 who is 69" tall NECK= 15 WAIST= 32 Circumference = 32-15= 17 then you cross with the table 69" with Circ of 17 then BF%= 14%

for FEMALE person 2 who is 69" tall NECK= 15 WAIST= 28 HIP=37 circumference = 37+28-15= 50 then you cross with the FEMALE table at 69" with circ of 50 then BF%=19% now unfortunately i dont have the female numbers in there yet because its tasking and takes forever to do.

also we dont use decimal's for the waist hip and neck, if your 15.5 then your 16 if your 15.4 then your 15. ect. we have to round it up or down.

Also, whats the reason/difference for the fields CircumCalc and Final and Remainder and FinalFullCalc?

[scoobz1234]

I got what i needed, thank you for all your help! my issue was that i had to change what the data was stored as IE NUMBER or TEXT. then i just remade the queries, 3 of them and got exactly what i needed, thank you!

[rpeare]

Sorry I forgot to mention I'd change data types to number (double)