need help in with code

[Guest]

I did try your suggesting with the select case and again itseems that it is
not working for me.maybe I should change something with the gender and age
but I am still did not find the rightway to do it
thanks irit

John Vinson suggested the lookup table. It seemed to me a better choice
than what I had suggested, but I am afraid I can't really advise you about
the best way to implement it. On my own I may be able to figure it out, but
I would have trouble providing useful suggestions and troubleshooting if
something goes wrong. Since John suggested the lookup table, questions
about it should be posted in his part of this thread.
In reviewing his postings in this thread I see that he suggested a way to
proceed, although I do not know to which query he referred, or just what he
meant by "non equi join". Sorry I can't be more helpful, but I will say
again that it wasn't my suggestion.
If you do not get a reply in a day or two, try posting a new thread in which
you ask specifically about using a lookup table. Good luck.

Thank you all.I was trying the lookup table but I got very confused.
I creadte the lookup table.Then I have to create a query with the lookup
tabel and my main table.
1.How in the qury I connect them and what field.
2.Where I right the sql in the qury:
yourtable.[AerobicPower] = [lookuptable].[PowerLow]

AND yourtable.[AerobicPower] >= lookuptable.[PowerLow] AND
yourtable.[AerobicPower] < lookuptable.[PowerHigh]

there is a way that I will get some exmaple.
thanks irit

My point was that you tested for the same gender and age variables in
each
expression, which is not necessary. Also, it is not necessary to use <
and
in the same line of code in this situation, since Access will stop
checking once the result evaluates to True. For instance:
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
' If AerobicPower is 55.1 or more, Access _
' won't get to this line of code. All you need _
' to do is check as shown
If Aerobicpower >= 52.1 And _
Gender = "2" And Age = "3" Then
Aerobic_Resylt = "80%-89%"

Note that I tested once for Gender = 2 and Age = 3, then checked within
that
result. I wasn't saying you don't need to check, but rather that you
don't
need to write the same code over and over.

I showed Select Case as a way of making the code more compact and easier
to
write. You could also have used ElseIf in your situation. Using nested
If
expressions will work, but it is much more difficult to manage changes to
the code if you need to add or remove something (which "If" does this
"End
If" go with?).

Both Select Case and ElseIf are described in more detail in Help, but
having
said that I think John Vinson's suggestion to use a lookup table is a
clean
and efficient way of managing this situation, and has a lot to recommend
it
over the approach of using code. My responses are intended to explain my
thinking and to point out some coding options when the situation arises,
rather than to advocate that you follow the coding route to solve the
problem.

Thank you.
I have another gender to check and I have to go through 5 groups of age
which again make the code very massive.for my understanding I need to
use
the
same code in the gender and the age control.Not becuase it needs to
change
by
age all the time (it is a one time test) but the usere can make a
mistake
with the age and all the others control already filled they need to be
updated and to match to the right result without the users go throuth
them
again.
hope that you ahve some suggestion
irit

:

I will just weigh in for a moment here to offer some observations, and
to
say that Select Case may help. Also, there's no need to test for
Gender
and
Age over and over. On that topic, if Gender is a text field, why not
use
Male or Female? If it is not a text field (for instance, if it is an
Option
Group), do not use quotes. Similarly, Age should probably be a
calculated
field based on Date of Birth, but if you are using an Option Group
then
it
is a number, so again, no quotes.

Having said that, maybe something like:
If Me.Gender = 2 and Me.Age = 3 Then
Select Case Me.AerobicPower
Case >= 55.1
Me.AerobicResult = "90%"
Case >= 52.1
Me.AerobicResult = "80% - 89%"
etc.
End Select
End If

However, there are a lot of other questions and considerations. If
Aerobic
Result is a calculation, it would probably be best to perform the
calulation
as needed. There are other age groups for which you need to test, and
one
other gender, which will complicate the code or the calculations. If
the
records are maintained for any length of time, what happens when
somebody
moves into another age bracket?

Thanks,
This is some of the coderivate Sub Aerobicpower_Change()
'male
If Aerobicpower >= 55.1 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "90%"
Else
If Aerobicpower >= 52.1 And Aerobicpower <= 55 And Gender = "2" And
Age
=
"3" Then
Aerobic_Resylt = "80%-89%"
Else
If Aerobicpower >= 49 And Aerobicpower <= 52 And Gender = "2" And
Age =
"3"
Then
Aerobic_Resylt = "70%-79%"
Else
If Aerobicpower >= 47.4 And Aerobicpower <= 48.9 And Gender = "2"
And
Age
=
"3" Then
Aerobic_Resylt = "60%-79%"
Else
If Aerobicpower >= 44.2 And Aerobicpower <= 47.3 And Gender = "2"
And
Age
=
"3" Then
Aerobic_Resylt = "50%-59%"
Else
If Aerobicpower >= 42.6 And Aerobicpower <= 44.1 And Gender = "2"
And
Age
=
"3" Then
Aerobic_Resylt = "40%-49%"
Else
If Aerobicpower >= 41 And Aerobicpower <= 42.5 And Gender = "2" And
Age
=
"3" Then
Aerobic_Resylt = "30%-39%"
Else
If Aerobicpower >= 37.8 And Aerobicpower <= 40.9 And Gender = "2"
And
Age
=
"3" Then
Aerobic_Resylt = "20%-29%"
Else
If Aerobicpower >= 34.6 And Aerobicpower <= 37.7 And Gender = "2"
And
Age
=
"3" Then
Aerobic_Resylt = "10%-19%"
Else
If Aerobicpower < 34.6 And Gender = "2" And Age = "3" Then
Aerobic_Resylt = "0%-9%"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

* For each field I have a code for different age and for the gender.
I have around 20 fields that I use the same idea of the code but
using
different numbers.
The code is on the field "Aerobicpower" on lost fucus or after
update .
1.Is there a way to shorter the code?
2.the result of the number that the user imput are showing in the
field
"Aerobic_Resylt" but,
I want the result changing every time the the user change the gender
or
age.
Here is my problem beacuse the codes are so big it does not let me
put
the
same code on the gender or the age.
How do I do that?

thanks again
Irit

:

On Mon, 15 Oct 2007 14:56:00 -0700, Imarko
<[email protected]>
wrote:

Can I send you one exmple of my code to your e-mail .It will be
really
helpful for me.

Well... ordinarily I reserve private EMail support to paying
clients.
I'm
a
self-employed consultant donating time on the newsgroup. Can you
perhaps
post
the code here?

If the code is not something you want in public, I'll make an
exception;
email
it to jvinson <at> wysard of info <dot> com (edit out the blanks
and
edit
in
the punctuation) and I'll take a look.

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you all.I was trying the lookup table but I got very confused.
I creadte the lookup table.Then I have to create a query with the lookup
tabel and my main table.
1.How in the qury I connect them and what field.
2.Where I right the sql in the qury:
yourtable.[AerobicPower] = [lookuptable].[PowerLow]

AND yourtable.[AerobicPower] >= lookuptable.[PowerLow] AND
yourtable.[AerobicPower] < lookuptable.[PowerHigh]

Please post the relevant fields of your table and the SQL view of the query
that you're trying; and indicate what errors or incorrect results you're
getting.

John W. Vinson [MVP]