Option Group Values

[VBAhole22]

I designed a database around a questionaire form. The form has 20 questions. Each question is an option group and the value gets stored in a table with the questions in the fields. Each record in the table is another person filling out the form. When I first started the values of the question answers (the options in the options group) were 0,1,2,3. I just gave the option the value of the answer. This worked fine. Then the boss wanted to score some answers as fractions like 0,1.5,2,3.5. I got around this by doubling the option value and then at the end halfing the totals. So a answer of 1.5 would get an option value of 3 and in the final tally score I would divide by 2. No problemo.

Now the boss wants scores like 1.4, 3.6 and he wants two of the answers to have the same value.

How can I get option values to be anything but integers?
How can I write the correct score to the table?
How can I get two options to have the same value without both of them being selected everytime one of them is?

Option Group Mayhem!!!!

 

[DALeffler]

Use base 10 for your option value values.

IOW, give the option value for your controls in the frame values like 1000, 1500, 2000, etc. and then divide the frames' value by 1000 to get 1, 1.5, 2, etc...

If you need two controls in the frame that both equate to, say 1.3, give one control an option value of 1300 and the other control an option value of 1301.

If a user selects either 130? control, you can divide the frame value by a hundred, coerce that result to an long integer, divide that long integer by 10 and save the result as a double...

HTH,

Doug.

 

[VBAhole22]

DA,

Thanks for the hint. It worked like a charm. One follow-up question if you are game:

The value of the option chosen in each group is written to the underlying table. I would like my 'score' value written instead of the base 10 option group value. How do I go about this? Would it be something like an event when the record closes and then all of these 'scores' get written into the table as a record? Or could I do it on each option group's afterUpdate?

Once again, thanks for your help on that one.

 

[DALeffler]

I would personally keep the value of the users' selection in the table and not what the value equates to.

If there is more than 1 control in a frame you need to equate to a value, it's probably inevitable that you'll eventually want to know how many times one control was selected over the other control(s) in the frame. Storing the value the frame selection equates to can destroy that information. And that means the form will not necessarily show the choices the user initially made when a form displays a record.

Example: You have 3 controls in an option group frame that need to equate to 1.5 (option value are 1501, 1502, 1503) and the form stores 1.5 in the bound table field after a user selects 1 of the 3 controls. As soon as 1.5 is written to the table, the form frame will show the control that has an option value of 2 (1.5 rounded to an integer) as the user selection. The frame won’t show a selection at all when there is no control in the frame with an option value of 2.

This is one example of the problems you can run into when saving calculated data.

You should use queries to perform calculations on data and then display the query results rather than trying to store the results of the calculation.

HTH,

Doug.

 

[VBAhole22]

I see where you are coming from DA and I agree with you totally. The issue I had with this method is that the boss wants to see the translated 'score' in a table and be able to export that table to Excel for further analyses. I agree this is better handled with a query that does the translation. Before your suggestion to use base 10 I was using a select case statement to do the translation. This technique falls apart when you have to build a query to display the true score because you can't (as far as I know) use a function in a query field, like you can in a report control.

I do appreciate your help and I think almost all of my issues have been solved. I just have need one more clarification. If I use your above suggestion (for multiple options with the same value) for dividing by 100 and then coercing: How would I go about doing that part in a query field? Also, are the 2 techniques you suggested (the divide by 1000 and the divide by 100 and coerce) mutually exclusive or could I do both in the same option group?

Thanks again for all the help you have given me so far.

 

[DALeffler]

Sure you can call functions in queries! Try something like this:

SELECT MyTblNme.RecID, MyTblNme.Frame1Slctn, ((CLng([MyTblNme]![Frame1Slctn]/100))/10) AS Frame1CalcV, MyTblNme.Frame2Slctn, ((CLng([Frame2Slctn]/100))/10) AS Frame2CalcV
FROM MyTblNme;

The CLng is a function call (it coerces the table value that’s divided by 100 to a long integer data type).

If you’re not familiar with SQL, your query design grid would look like (based on the above SQL):

Field: RecID
Table: MyTblNme
Show: Yes

Field: Frame1Slctn
Table: MyTblNme
Show: Yes

Field: Frame1CalcV: ((CLng([MyTblNme]![Frame1Slctn]/100))/10)
Show: Yes

Field: Frame2Slctn
Table: MyTblNme
Show: Yes

Field: Frame2CalcV: ((CLng([MyTblNme]![Frame2Slctn]/100))/10)
Show: Yes

And to get the query result into an Excel file, show the Queries tab in the database window, highlight the above query by clicking on it once. Then from the File drop down menu, choose “Save as/Export…” and follow the instructions in the dialog boxes. Viola! There’s you’re Excel file for the boss.

HTH,

Doug.

 

[VBAhole22]

Sweeeeeetttt!!!!!!!

I can handle that.

Many thanks Doug.