Calculations in text boxes

[markdooler]

Hi Everyone.

Need some more advise please.

I have tblA which has a filed for NPS.

There are 3 possible entries to that field, Promotor, Passive and Detractor.

These are inputted by the user.

i now need to calculate what the NPS score would be and display it in the split view form.

I would like it done in a text box so the score self calculates when filters are applied.

This can be done in 2 ways.

1, attribute a 100 score for promotor, 0 score for passive and -100 score for detractor. Add them together and calculate the average.

2, % promotors - % of detractors

So far i have tried to attribute the score and can get it to calculate. I can do a count to get total responses but when i try to divide the score by the responses i get a #Num! error

Please see below for more detail

Any help would be appreciated.

 

[pbaldy]

Are you sure those are the 2 textbox names? The "Tot" vs "Total" isn't consistent, which may mean one is wrong.

 

[markdooler]

Are you sure those are the 2 textbox names? The "Tot" vs "Total" isn't consistent, which may mean one is wrong.

Yea, they were thrown together as a test of concept lol.

If i can get it working i will standardise them.

 

[pbaldy]

Oh, the standardization isn't causing an error, it just made me wonder. Can you attach the db here? What you have looks like it should work.

 

[markdooler]

Oh, the standardization isn't causing an error, it just made me wonder. Can you attach the db here? What you have looks like it should work.

I did fix the standardisation just to check.

Its difficult to post here as it links to a sharepoint list with sensative data etc

 

[pbaldy]

I understand, we certainly don't want sensitive data. Where exactly are the textboxes (what section)? I just did a brief test with 3 textboxes in the form footer and it worked as expected. The first 2 had Sum and Count calculations, the 3rd did the division.

 

[markdooler]

I understand, we certainly don't want sensitive data. Where exactly are the textboxes (what section)? I just did a brief test with 3 textboxes in the form footer and it worked as expected. The first 2 had Sum and Count calculations, the 3rd did the division.

They are all in the header

Can you post examples of yours?

 

[pbaldy]

This is just a crude db somebody posted to test something else. I added the formulas.

 

[Galaxiom]

There are 3 possible entries to that field, Promotor, Passive and Detractor.

These are inputted by the user.

Then you can expect problems with spelling mistakes.
A better strategy is to have the choices selected from a combobox with Limit To List.
Better to store the values a Ids. This allows the displayed description to be easily changed.

The table that provides the RowSource for the combo and also hold the scores for the choices.
This way more options can be added and the scores adjusted by simply inserting or updating records in the RowSource table instead of having to go into design view.

 

[markdooler]

Then you can expect problems with spelling mistakes.
A better strategy is to have the choices selected from a combobox with Limit To List.
Better to store the values a Ids. This allows the displayed description to be easily changed.

The table that provides the RowSource for the combo and also hold the scores for the choices.
This way more options can be added and the scores adjusted by simply inserting or updating records in the RowSource table instead of having to go into design view.

Sorry i should have said.

The 3 options are selected by combo boxes. There is a table that holds the 3 options that the combo box uses to create the selection so spelling etc will never be an issue.