Creating Expressions or code using the code builder. Please Help M

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Morning, Afternoon and Evening.

I need help from you and after checking some responces i know you all can
help. I am new to Access and Databases, i have taught myself in a matter of 2
weeks and have created what i believe to be a fully functional, normalised
database. (Although i am sure improvments could be made).

I have created a Call Monitoring Database. Which is designed to track
whether advisors maintain a level of quality that our clients require. We are
only a small business, so no IT dept can assist.

I have set up Option groups within the form which inputs a value (the value
the questions whether right or wrong is worth) I.E question 1 is worth 1
point. a tick in the yes box will input a 1 in the field(again in the tbl)
the form is based on. If it is a no it will enter a -1 figure.

Now the problem. I use text boxes to calculate the number of questions which
are worth 1 point 2 point and so on, but find myself using so far 15 text
boxes to try and get a calculation. I have been advised to use an expression
or the code builder ( so working with the VB code itself). How do i replicate
the same calculations in an expression or code?

I look forward to your replies. Thank you for taking the time to read this
even if you are unable to help me.
 
Please post the calculations you currently have and we'll see if we can
combine them into something simpler for you. Also, do you need to see the
results of each intermediate step or do you only need to see the final
answer?
 
Well i will need to see the final result only as a percentage.

I have used 3 levels to try and calculate.
There are 3 text boxes (Basic Levels)

Text328 (Adds the number of questions that worth 1 point, which have been
answered correctly)
Text330 (Adds the number of questions that worth 2 points, which have been
answered correctly)
Text324 (Adds the number of questions that worth 3 points, which have been
answered correctly)

Intermediate lvl Text boxes - I have 2
Text332 (takes figure from Text330 and times the number by 2)
Text334 (takes figure from Text324 and times the number by 3)

High Level (Heres the problem) - I currently have 1 text box
Text336 (Adds figure in Text332 and 328 together)
When i try the same thing with another text box i.e Text336 + 334 all i get
is "#Name?" as afigure.

I hope this helps but to sumerise - i have a total of 23 questions.
1 question worth 1 points
10 questions worth 2 points
10 questions worth 3 points
1 question worth 4 points
1question worth 5 points

All questions answered yes will add a Positive figure in the linked field,
if no they get the same value but as a negative value. If N/A then its a 0
and doesn't count against or for the total score.
So i assumse i calculate the number of yes answears first (Easily Done) but
then i need the total number ofr yes and no's combines to do the yes / Total
YesNo answers * 100 = % score.

Thank you all....
 
Ok, how is this data being stored (i.e. what's done with it when the user
selects an answer)? Are these stored or are you just calculating from the
selections on the form and storing the result? The equations you have to do
your current calculations may help, where to Text323, Text330, and Text324
get their values?

The #Name error will result if Access can't resolve the name of an item in
the equation. This can happen if you mistype the name or if the textbox and
the field it is bound to both have the same name. If the Control Source is
really "Text336 + 334", should it be "=Text336 + Text334"?
 
I have used an option group which is directly linked to the table, and the
appropriate field in the table. I.E the table contains 23 fields each labeled
q1 q2 etc.

So in the form (default value for option goup for each question is N/A - 0
in other words) You would go down the questions and click on the option box
in the same row as the questions, to which it would enter the number the
question was worth positive if yes and negative if no. This would literaly be
entered into the table. I then also have a standard text box for each field
from this table which represent the questions in order. so when you click on
yes on the form, a text box linked also to the feild from the table would
populate the figure.

The text boxes i described before then pick up these figures by for example
=([Q2])+([[Q3]).

So what i am looking for is away if possible to remove the text boxes and
use a code in just one box. Have i described myself well?

I can email over a watered down example of what i mean if need be to show
you what i mean....

Thnxs again for the reply...
 
If TextboxA = Q1+Q2 and TextboxB = TextboxA + Q3, then you could also write
TextboxB = Q1+Q2+Q3 and get rid of TextboxA. Any legitimate mathematical
expression will work. Where you may run into a problem is if you have a
textbox that is also called Q1, then Access doesn't know which Q1 to use and
will give a #Name error. This will happen even if the textbox named Q1 is
bound to the field Q1, so that they are both the same value.

If you want to email a copy, that'll be fine. Please zip it first to get it
by the file extension checkers and send it to (e-mail address removed).

--
Wayne Morgan
MS Access MVP


Access to Access said:
I have used an option group which is directly linked to the table, and the
appropriate field in the table. I.E the table contains 23 fields each
labeled
q1 q2 etc.

So in the form (default value for option goup for each question is N/A -
0
in other words) You would go down the questions and click on the option
box
in the same row as the questions, to which it would enter the number the
question was worth positive if yes and negative if no. This would literaly
be
entered into the table. I then also have a standard text box for each
field
from this table which represent the questions in order. so when you click
on
yes on the form, a text box linked also to the feild from the table would
populate the figure.

The text boxes i described before then pick up these figures by for
example
=([Q2])+([[Q3]).

So what i am looking for is away if possible to remove the text boxes and
use a code in just one box. Have i described myself well?

I can email over a watered down example of what i mean if need be to show
you what i mean....

Thnxs again for the reply...

Wayne Morgan said:
Ok, how is this data being stored (i.e. what's done with it when the user
selects an answer)? Are these stored or are you just calculating from the
selections on the form and storing the result? The equations you have to
do
your current calculations may help, where to Text323, Text330, and
Text324
get their values?

The #Name error will result if Access can't resolve the name of an item
in
the equation. This can happen if you mistype the name or if the textbox
and
the field it is bound to both have the same name. If the Control Source
is
really "Text336 + 334", should it be "=Text336 + Text334"?
Click to expand...
Click to expand...
 
Back
Top