Link to home
Start Free TrialLog in
Avatar of SOOPERDAD
SOOPERDAD

asked on

Declare an array? Help with VBA code

Ok, I have no idea how to word this, so I'll start with my table.. this is what the table looks like when you are viewing the data....

SitUp Reps      17-21      22-26      27-31      32-36 <-----These are age groups
0                0             0              0              0
21              9             21            34             35
22             10            23            35             36<----This is the person's score based off
23             12            24            36             38         how many sit ups he/she did
24             14            25            37             39
25             15            27            38             40
26             17            28            39             41  // the SitUp Reps column is the number of sit ups the
27             18            29            41             42  // person did

What I need is to build a function that takes a person's age from a textbox on a form and depending on which age group that age falls into,
create a string ("17-21" or "22-26" etc) that will be used to decide which field is shown in a query.

:)
Chris
SOLUTION
Avatar of ahmedbahgat
ahmedbahgat

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SOOPERDAD
SOOPERDAD

ASKER

So, as long as the data stored in the age box is of integer type, the select case will know if that number falls between 17-21, etc?
Disregard the above post,, lol...
yes the select will know if the age will fall with that range, it is faster than If Then

cheers
lol

cheers
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
nick the question by the poster did not mention what you are saying, so would that mean I'm in fault or somethin, and if so why the quetion was posted again, unless the poster could not work out the first answer

mate you are really obssesed :-)

cheers
ahmedbahgat,
Your answer is perfect for this question, Nick is helping me with the bigger picture, I'm learning everything as I go here and at the moment know enough to fill a thimble when it comes to using vba.. you will get full points for your answer, I just have to be babystepped through this stuff right now(I'm feeling really dumb atm...)

Nic,

I think it's starting to come together in my head, I would put ahmedbahgat's select case inside your function right?
Also, with three fields on my form, age, repetitions, and score, where do i call the function? wait,, hmmm.. on the after update of the repititions field, and then in the same code, set the value of the score field to the value returned from the function? lol,, i'm like a blind man feeling my way around here...

Chris
The names don't "match" anymore (the sample has different headings), but the "problem" is the extraction of the correct  score where the table's fieldnames effectively hold the range where the age needs to fit in and the (not mentioned) situps/Repetitions will also be needed to extract the correct row.

obsessed with what mate? <LOL>

Nic;o)
Oops crossing comments, my previous one was for ahmedbahgat

Chris:
> I would put ahmedbahgat's select case inside your function right?
Yes, but to assign the value from the recordset (rs!....)
My previous comment already stated you also need the number of situps and they are fetched in the SET rs statement !
Just think what to do when the number (like 10 or 29/30/etc.) isn't present in your table.......

Nic;o)
nico do not get me wrong, frankly I do not care about points, but I care more about how experts deal together, and if you read my profile, you will see that I make 30,000 points a month and I'm absolutly delighted with that as long as it is more than 3000 points

however on the other hand there is plenty of experts that make zillions of points a month and never give a chance to new comers


Chris, I appreciate your matured comment


cheers
Is there a difference between
Case 17 to 21
and
Case between 17 and 21  ?

Or is this just two ways to do one thing?
ahmedbahgat, I don't care about the points either, but took my comment here to keep the problem solving in one location and was hoping that you and/or other experts can add with their own expertise.
Having a similar problem solved in two locations would otherwise become very confusing for Chris.

My function proposal has been created in a "rush" and it will certainly be possible to improve it.
That's where I hope you can help us with.

Nic;o)
cheers nico appreciated
Sorry for the confusion guys, I guess I am combining two questions, ahmedbahgat answered the question on this post perfectly, I was thinking I had to create a string and use that string in a query that would grab the correct age field, I do not know enough about working with recordsets yet, and therefore did not know that I could just get the field using Nic's suggestion. Thank you both for your time, I promise both your efforts are truly appreciated.

Chris
chris, not sure about 97 and 2k but under xp, case between a and b will produce an error

cheers
See that your help is needed ahmedbahgat :-)

Guess I need to get some sleep as it's far past midnight here, see you!

Nic;o)
have a good dreams, we just started our day here in oz, we all here to help and learn

cheers