Calculated form field control not working: Access 2000 Prm

[Dököll]

Hello Hello!

Hope it's a good week-end near you...

I am trying to substract then divide through a form field to no avail.

What do you make of it?

(1) Achieved form grabs a certain number of instances for specific fields, Compensation Question field, as one example, having many instances where a Compensation Question was raised

(2) QuestionQuestions subform collects data specific to days where Compensation Question not answered, being used to substract from Achieved

 
=[Forms]![Achieved]![Compensation Question]-[Forms]![QuestionQuestions subform]![Compensation Question]/[Forms]![Achieved]![Compensation Question]
 
 

I think the problem is I cannot get my form data to communicate with its subform. I beat it up quite a bit an the machine goes mute on me.

Would you tell me what you see? Thanks, and enjoy your week-end:-)

Dököll

[JConsulting]

Hello Hello!

Hope it's a good week-end near you...

I am trying to substract then divide through a form field to no avail.

What do you make of it?

(1) Achieved form grabs a certain number of instances for specific fields, Compensation Question field, as one example, having many instances where a Compensation Question was raised

(2) QuestionQuestions subform collects data specific to days where Compensation Question not answered, being used to substract from Achieved

 
=[Forms]![Achieved]![Compensation Question]-[Forms]![QuestionQuestions subform]![Compensation Question]/[Forms]![Achieved]![Compensation Question]
 
 

I think the problem is I cannot get my form data to communicate with its subform. I beat it up quite a bit an the machine goes mute on me.

Would you tell me what you see? Thanks, and enjoy your week-end:-)

Dököll

if you think your logic and values will work...you might try this syntax

[Forms]![Achieved]![Compensation Question]-
[Forms]![QuestionQuestions subform].Form.[Compensation Question]
/[Forms]![Achieved]![Compensation Question]

[Dököll]

if you think your logic and values will work...you might try this syntax

[Forms]![Achieved]![Compensation Question]-
[Forms]![QuestionQuestions subform].Form.[Compensation Question]
/[Forms]![Achieved]![Compensation Question]

Much appreciated...

Looks like we need to have another look, it does not seem to work at my end of it. It may be just a simple thing, that's what's a bit of an annoyance. Does it accurately substract then divide near you?

You know what! Perhaps I should try:

 
[Query]![Achieved]![Compensation Question]-
[Query]![QuestionQuestions]![Compensation Question]
/[Query]![Achieved]![Compensation Question][/quote]Much appreciated...
 
 

I am not sure if this will work, just thinking outloud:-)

Thanks, JConsulting, in a bit, I think I'll try it, see what happens!

[Dököll]

Much appreciated...

Looks like we need to have another look, it does not seem to work at my end of it. It may be just a simple thing, that's what's a bit of an annoyance. Does it accurately substract then divide near you?

You know what! Perhaps I should try:

 
[Query]![Achieved]![Compensation Question]-
[Query]![QuestionQuestions]![Compensation Question]
/[Query]![Achieved]![Compensation Question]

Much appreciated...
 
 

I am not sure if this will work, just thinking outloud:-)

Thanks, JConsulting, in a bit, I think I'll try it, see what happens![/quote]

It did not work either through query, weird:

 
=[Achieved]![Compensation Question]-[QuestionQuestions]![Compensation Question]/[Achieved]![Compensation Question]
 
 

[JConsulting]

Much appreciated...

[/code]

I am not sure if this will work, just thinking outloud:-)

Thanks, JConsulting, in a bit, I think I'll try it, see what happens!

It did not work either through query, weird:

 
=[Achieved]![Compensation Question]-[QuestionQuestions]![Compensation Question]/[Achieved]![Compensation Question]
 
 

[/quote]


The sample I gave you earlier included the "Form" operator in the string that references the subform value. If that did not work then the way you're trying to use it won't.

Here's what you do. Create a textbox on the main form. Call it SubCompQ or something like that.

Now, on the subform, on the On_Current event, set the value of that new textbox on the main form like this

me.Parent.SubCompQ = me.[Compensation Question]

now instead of trying to use the subform value...use your new field

=[Achieved]![Compensation uestion]-
me.[SubCompQ]/
[Achieved]![Compensation Question]

Hope this makes sense..
J

[Dököll]

It makes plenty of sense J, genius, and I am going to try your example but please read on, pretty similar to yours...

I did figure out that I am just a bonehead. Your examples previously should have worked; not in query but at least with form data. I do not know what went on with the query but:

 
=[Achieved].Form!Compensation Question
 
 

that should have worked...

What I did not tell you, because I did not know this up till Friday, sixish, is that Compesation Question is the Control Source, I needed to reference the field name (the textbox name), thus:

 
=[Achieved].Form!CompensationQ
 
 

Just like you said:-)

I can't keep it straight though. I do not understand when it is necessary to use the field name. And it looks like this only work with the subform data, the form itself is fine. For instance:

 
=[Achieved].Form!Compensation Question
 
 

this works on the form (Achieved). Not on for subform, even though the form and subform Control Source has the same name (Please throw your hat in here, perhaps subform fields should be specific and different from form itself)...

...going forward, I will try your example because it seems cleaner. This is the result thus far:

 
=([Achieved].Form![Compensation Question] - [QuestionQuestion].Form![CompensationQ])/([Achieved].Form![Compensation Question]) 
 
 

I was also getting weird results, I figured out I did not enclose in parenthesis so calculations are handled seperately, again, I am a bonehead:-)

[ADezii]

Hello Hello!

Hope it's a good week-end near you...

I am trying to substract then divide through a form field to no avail.

What do you make of it?

(1) Achieved form grabs a certain number of instances for specific fields, Compensation Question field, as one example, having many instances where a Compensation Question was raised

(2) QuestionQuestions subform collects data specific to days where Compensation Question not answered, being used to substract from Achieved

 
=[Forms]![Achieved]![Compensation Question]-[Forms]![QuestionQuestions subform]![Compensation Question]/[Forms]![Achieved]![Compensation Question]
 
 

I think the problem is I cannot get my form data to communicate with its subform. I beat it up quite a bit an the machine goes mute on me.

Would you tell me what you see? Thanks, and enjoy your week-end:-)

Dököll

Your problem is both Syntax and Order of Precedence related. The Division operation will be executed prior to the Subtraction operation, because it has a higher priority (Order of Precedence).

Debug.Print 18 - 10  / 4 will evaluate to 15.5 not 2 
Debug.Print ((18 - 10) / 4) will evaluate to 2     'the correct result
 

The Default behavior can be overwritten with nested parenthesis. The operation within the inner parenthesis, Subtraction, will be performed prior to the Division. Note the syntax change in referring to the Sub-Form Control, also:

=(([Forms]![Achieved]![Compensation Question]-[Forms]![QuestionQuestions subform].Form![Compensation Question])/[Forms]![Achieved]![Compensation Question])

[Dököll]

It did not work either through query, weird:

 
=[Achieved]![Compensation Question]-[QuestionQuestions]![Compensation Question]/[Achieved]![Compensation Question]
 
 

The sample I gave you earlier included the "Form" operator in the string that references the subform value. If that did not work then the way you're trying to use it won't.

Here's what you do. Create a textbox on the main form. Call it SubCompQ or something like that.

Now, on the subform, on the On_Current event, set the value of that new textbox on the main form like this

me.Parent.SubCompQ = me.[Compensation Question]

now instead of trying to use the subform value...use your new field

=[Achieved]![Compensation uestion]-
me.[SubCompQ]/
[Achieved]![Compensation Question]

Hope this makes sense..
J[/quote]Yes it does make sense, J, looks like I need to take a closer look:

 
me.Parent.SubCompQ = me.[Compensation Question]
 
 

Do you mean to say there is an event/procedure area called On_Current?

I like your idea, I would like to try it if you could go a little more into it.

Am I right clicking the form or the field?

I am sure this silly a question, it's probably right there and I can't see it. Thanks!

[Dököll]

The Default behavior can be overwritten with nested parenthesis. The operation within the inner parenthesis, Subtraction, will be performed prior to the Division. Note the syntax change in referring to the Sub-Form Control, also:

=(([Forms]![Achieved]![Compensation Question]-[Forms]![QuestionQuestions subform].Form![Compensation Question])/[Forms]![Achieved]![Compensation Question])

You are correct, ADezii...I am sure you saw my reply to JConsulting previously, but I wanted to thank you. Looks like I am on the right path with it. Thanks!

[ADezii]

You are correct, ADezii...I am sure you saw my reply to JConsulting previously, but I wanted to thank you. Looks like I am on the right path with it. Thanks!

You are quite welcome.

[Dököll]

I am just throwing this out there while I fetch here, may have been added. I want to, within the same form, add another subform to collect other data, then look up Compensation Question field to see if values are zero, if so, load zero, but if values are higher than zero, to look up teh other subform to see if value there is set to TRUE (a checkbox field), then reflect zero for Compensation Question field:

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question]))
 
 

Not sure how to continue this to grab the additional field, set to TRUE, to compute a zero when TRUE is found there (again, TRUE is a selected checkbox)

In therory, below looks pretty:

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question])) 
 
OR
 
IIf(IsNull(CheckMeQuestions subform.Form![Compensation Question]),0,(CheckMeQuestions subform.Form![Compensation Question]))
 
 

Here is what it sounds like in my head:

If Compensation Question in QuestionQuestions subform = 0 Then
Compensation Question = 0

ElseIf Compensation Question in QuestionQuestions subform = 1

And Compensation Question field in CheckMeQuestions subform = TRUE Then

End if

Slowly repeating myself:

I am looking to see if I can plug it in the Conpensation Question field on the form as...

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question])) 
 
 

to get a zero, wherever a TRUE is recorded for CheckMeQuestions subform data

Hope this makes sense... will search meanwhile:-)

[Dököll]

I am just throwing this out there while I fetch here, may have been added. I want to, within the same form, add another subform to collect other data, then look up Compensation Question field to see if values are zero, if so, load zero, but if values are higher than zero, to look up teh other subform to see if value there is set to TRUE (a checkbox field), then reflect zero for Compensation Question field:

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question]))
 
 

Not sure how to continue this to grab the additional field, set to TRUE, to compute a zero when TRUE is found there (again, TRUE is a selected checkbox)

In therory, below looks pretty:

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question])) 
 
OR
 
IIf(IsNull(CheckMeQuestions subform.Form![Compensation Question]),0,(CheckMeQuestions subform.Form![Compensation Question]))
 
 

Here is what it sounds like in my head:



Slowly repeating myself:

I am looking to see if I can plug it in the Conpensation Question field on the form as...

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question])) 
 
 

to get a zero, wherever a TRUE is recorded for CheckMeQuestions subform data

Hope this makes sense... will search meanwhile:-)

Did not find anything...


All is it is, rather than saying:

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,1) 
 
 

I want it to say:

 
=IIf(IsNull(QuestionQuestions subform.Form![Compensation Question]),0,(QuestionQuestions subform.Form![Compensation Question])) 
 
 

Thus, I would get the number in ,(QuestionQuestions subform.Form![Compensation Question]) field as opposed to a 1

Does this makes sense?