Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    75

    Unanswered: Return "score"; Dlookup?

    Really, I've asked this question too many times to feel sane, but it's been MONTHS, and I have yet to resolve this.

    I want to grade a physical fitness test. I have 3 tables, one each for push ups, sit ups, and a run, consisting of "Repetitions" and several fields whose names reflect a combination of age group (ex, 23-35 are group 2) and gender (ex of field name: "1M"). If a person in category "1M" does 23 repetitions of push ups, I need it to return the corresponding point value (also a number) from the appropriate field (like "1M"). I already have a field in my base table that assigns the group/field name.

    What is the easiest way to achieve returning scores? I have tried and tried and tried and tried to use Dlookup to no avail.

    An example of my most recent attempt:

    Private Sub PTRPPURaw_AfterUpdate()
    Dim str1, str2 As String
    Dim num1 As Integer
    str1 = "'[" & [Group] & "]'"
    num1 = [PTRPPURaw]
    str2 = Chr(34) & "[Repetitions] >= " & num1 & Chr(34)
    [PTRPPUPts] = DLookup(str1, "PUTable", str2)
    End Sub

    Is there some other way to do this? Or can someone at least help me fix the Dlookup?

    Please, please, please help.

    Thank you.
    Last edited by sublimsos; 02-23-04 at 16:13.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Please provide the structural details of the tables in question.

    Example,

    TableA(columnA, columnB)
    TableB(columnA, columnB)

    Desired Results
    ...
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Return "score"; Dlookup?

    Originally posted by sublimsos
    Really, I've asked this question too many time to feel sane, but it's been MONTHS, and I have yet to resolve this.

    I want to grade a physical fitness test. I have 3 tables, one each for push ups, sit ups, and a run, consisting of "Repetitions" and several fields whose names reflect a combination of age group (ex, 23-35 are group 2) and gender (ex of field name: "1M"). If a person in category "1M" does 23 repetitions of push ups, I need it to return the corresponding point value (also a number) from the appropriate field (like "1M"). I already have a field in my base table that assigns the group/field name.

    What is the easiest way to achieve returning scores? I have tried and tried and tried and tried to use Dlookup to no avail.

    An example of my most recent attempt:

    Private Sub PTRPPURaw_AfterUpdate()
    Dim str1, str2 As String
    Dim num1 As Integer
    str1 = "'[" & [Group] & "]'"
    num1 = [PTRPPURaw]
    str2 = Chr(34) & "[Repetitions] >= " & num1 & Chr(34)
    [PTRPPUPts] = DLookup(str1, "PUTable", str2)
    End Sub

    Is there some other way to do this? Or can someone at least help me fix the Dlookup?

    Please, please, please help.

    Thank you.
    This sounds simple to do but your explanation is hard to follow ... Can you clarify? (Basically put some table and column names in ...)

    AND THIS LINE looks erroneous to say the least:

    [PTRPPUPts] = DLookup(str1, "PUTable", str2)

    What are you trying to assign the result to?

  4. #4
    Join Date
    Jan 2004
    Posts
    75
    Ha, hard not to confuse others when I confuse myself!
    (My naming conventions are not the best... please overlook)

    I am only going to list the columns that are relevant.

    Tables:
    -Data(PTRPPURaw, PTRPPUPTs, PTRPSURaw, PTRPSUPts, PTRPRunRaw, PTRPRunPts, Group)
    -PUTable(Repetitions, 1M, 2M, 3M, 4M, 1F, 2F, 3F, 4F)
    -SUTable(Repetitions, 1M, 2M, 3M, 4M, 1F, 2F, 3F, 4F)
    -RunTable(Repetitions, 1M, 2M, 3M, 4M, 1F, 2F, 3F, 4F)

    Data.Group value will be 1M, 2M, 3M, 4M, 1F, 2F, 3F, or 4F

    All fields are numbers except for group which is text.

    When I type a value into data.PTRPPURaw, I want to assign the value of the lookup to data.ptrppupts.

    hopefully that is a little clearer??

  5. #5
    Join Date
    Jan 2004
    Posts
    75
    Let me add more...

    This:

    Private Sub PTRPPURaw_AfterUpdate()
    [PTRPPUPts] = DLookup("[1M]", "PUTable", "[Repetitions] = 23")
    End Sub

    returns 34 which is correct if a person in group "1M" does 23 pushups.

    This is what I'm trying to achieve but with the added ability to choose the field I get the information from and to choose the repetitions all based on the information inputed into the form.

  6. #6
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by sublimsos
    Ha, hard not to confuse others when I confuse myself!
    (My naming conventions are not the best... please overlook)

    I am only going to list the columns that are relevant.

    Tables:
    -Data(PTRPPURaw, PTRPPUPTs, PTRPSURaw, PTRPSUPts, PTRPRunRaw, PTRPRunPts, Group)
    -PUTable(Repetitions, 1M, 2M, 3M, 4M, 1F, 2F, 3F, 4F)
    -SUTable(Repetitions, 1M, 2M, 3M, 4M, 1F, 2F, 3F, 4F)
    -RunTable(Repetitions, 1M, 2M, 3M, 4M, 1F, 2F, 3F, 4F)

    Data.Group value will be 1M, 2M, 3M, 4M, 1F, 2F, 3F, or 4F

    All fields are numbers except for group which is text.

    When I type a value into data.PTRPPURaw, I want to assign the value of the lookup to data.ptrppupts.

    hopefully that is a little clearer??
    You will need to do a DMax or DMin instead of DLookup... Let me think this through...

    Say you input 50 pushups for a person who falls into the 1M group...
    You want to go to the table and find the record where the number in the table is lower than the 50... This could return more than one result if you have scores for 20 pushups, 30 pushups, 45 pushups, 55 pushups, etc... but you only want the record with 45... right?... So you want the Max record that is still under the amount of repetitions... Make sense?...

    So you need to do... (in the control source of the text box that will hold the score result)

    =DMax("'[" & [Group] & "]'", "PUTable", "'[Repetitions] <= " & [PTRPPURaw] & "'")

    HTH

  7. #7
    Join Date
    Jan 2004
    Posts
    75
    Trudi, you understand! Thank you! Yes, that is what I'm trying to do... I never thought to use Dmax.

    It looks perfect... but it returns the following error message: "The value you entered isn't valid for this field"

    Why??

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by sublimsos
    Trudi, you understand! Thank you! Yes, that is what I'm trying to do... I never thought to use Dmax.

    It looks perfect... but it returns the following error message: "The value you entered isn't valid for this field"

    Why??
    Hmmmm ... If the values in the Repetitions field are numeric... I'm thinking that it may be that it's thinking that [PTRPPURaw] is text (because it is being input into a text box)... Maybe we should explicitly convert it to an integer within the expression... Try this...

    =DMax("'[" & [Group] & "]'", "PUTable", "'[Repetitions] <= " & CInt([PTRPPURaw]) & "'")

  9. #9
    Join Date
    Jan 2004
    Posts
    75
    Same error message....

    The odd thing is... When I Debug, every part of the expression is returning the correct value, but the over expression results in null...

    this is exactly what I have:

    Private Sub PTRPPURaw_AfterUpdate()
    [PTRPPUPts] = DMax("'[" & [Group] & "]'", "PUTable", "'[Repetitions] <= " & CInt([PTRPPURaw]) & "'")
    End Sub


    I rechecked the field types for everything involved... the only difference I see is that data.ptrppupts and data.ptrppuraw are long integer and the fields in PUTable are Integer... even when I change them to match, I get the same error.

    Am I nuts??

  10. #10
    Join Date
    Jan 2004
    Posts
    75
    Still baffled!

    If I type in the expression, like "[1F]", then I don't get the error message. Does this mean that it is impossible to use a variable to determine which field to look in? Or does that mean my snytax is all wrong?

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by sublimsos
    Still baffled!

    If I type in the expression, like "[1F]", then I don't get the error message. Does this mean that it is impossible to use a variable to determine which field to look in? Or does that mean my snytax is all wrong?
    This thing is fighting us isn't it?? lol

    Do me a favour and do a Debug.Print on the statement...

    Try putting the whole Dmax statement into a string variable and then do a Debug.Print for me... and then post it... I wanna see what it comes up with...

  12. #12
    Join Date
    Jan 2004
    Posts
    75
    Trudi, you're getting over my head now...

    How do I do a debug.print??

  13. #13
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by sublimsos
    Trudi, you're getting over my head now...

    How do I do a debug.print??
    Forget that for now... I've been trying things out... The [PTRPPUPTS] is a text box on your form, correct? If so, try this...

    Me!PTRPPUPTS.ControlSource = "=DMax('[" & Me!Group & "]', 'PUTable', '[Repetitions] <= " & CLng(Me!PTRPPURaw) & "')"
    Last edited by Trudi; 02-11-04 at 17:57.

  14. #14
    Join Date
    Jan 2004
    Posts
    75
    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!

    I'm so happy I could cry!

    You're a genius!!

    It works!!!

    Thank you, thank you, thank you!!

    I've literally been working on this for months... pathetic, isn't it?

  15. #15
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by sublimsos
    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!

    I'm so happy I could cry!

    You're a genius!!

    It works!!!

    Thank you, thank you, thank you!!

    I've literally been working on this for months... pathetic, isn't it?
    Awww... I'm so glad...

    Well notice that I just made a quick change... I saw that it didn't have the less than sign in the statement... Make sure you have it in there...

    Good Luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •