VBA Help

[killermonkey]

I was wondering if someone would be able to point me in the right direction here. I've got some VBA code as seen below

Public Function PPnts(PUs) As Single
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("PFT")


If rst![gender] = "M" And rst![Age] > 16 <= 29 Then
    PPnts = DLookup("POINTS", "29MPUSHUPS", "PUSHUPS='" & PUs & "'")
ElseIf rst![gender] = "M" And rst![Age] >= 30 <= 39 Then
    PPnts = DLookup("POINTS", "30-39M PUSHUPS", "PUSHUPS='" & PUs & "'")
ElseIf rst![gender] = "M" And rst![Age] >= 40 <= 49 Then
    PPnts = DLookup("POINTS", "40-49M PUSHUPS", "PUSHUPS='" & PUs & "'")
ElseIf rst![gender] = "M" And rst![Age] >= 50 <= 59 Then
    PPnts = DLookup("POINTS", "50-59M PUSHUPS", "PUSHUPS='" & PUs & "'")
ElseIf rst![gender] = "M" And rst![Age] >= 60 Then
    PPnts = DLookup("POINTS", "60M PUSHUPS", "PUSHUPS='" & PUs & "'")
End If
End Function

Now it works to an extent except that it is only returning results from the "29MPUSHUPS" table as opposed to verifying the age and selecting the appropriate table before returning the corresponding points value. If anyone can help that would be awesome as I've been trying to figure it out for hours now.


Thanks in advance.

[ssanfu]

 If rst![gender] = "M" And rst![age] > 16 <= 29 Then

You can't do this. Each age value must be explicitly compared to a field

If rst![gender] = "M" And (rst![age] >16 and rst![age] <=29) Then

[killermonkey]

ssanfu...I can't express my thanks enough.

[ssanfu]

I modified your code.... Sometimes I just can't help it.
Try this:

Public Function PPnts(PUs) As Single
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("PFT")

   If rst![gender] = "M" Then
      If rst![age] > 16 And rst![age] <= 29 Then
         PPnts = DLookup("POINTS", "29MPUSHUPS", "PUSHUPS= '" & PUs & "'")
      ElseIf rst![age] >= 30 And rst![age] <= 39 Then
         PPnts = DLookup("POINTS", "30-39M PUSHUPS", "PUSHUPS='" & PUs & "'")
      ElseIf rst![age] >= 40 And rst![age] <= 49 Then
         PPnts = DLookup("POINTS", "40-49M PUSHUPS", "PUSHUPS='" & PUs & "'")
      ElseIf rst![age] >= 50 And rst![age] <= 59 Then
         PPnts = DLookup("POINTS", "50-59M PUSHUPS", "PUSHUPS='" & PUs & "'")
      ElseIf rst![age] >= 60 Then
         PPnts = DLookup("POINTS", "60M PUSHUPS", "PUSHUPS='" & PUs & "'")
      End If
   End If

   'clean up
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing

End Function

Looking at your code snippet, it appears that you have 5 tables that should be 1 table with an additional field (or 2) for the age.
Are the fields the same for the 5 tables?

[June7]

Is that recordset opening with a single record? Could it possibly not have any records?

I agree with Steve, looks like 5 tables that should be one table with another field for the age category.

[killermonkey]

Steve,

I do have 5 tables, one for each age bracket. The problem I was having is that depending on the age bracket you're in the amount of pushups you do gives you a different set of points. For example if you're under 30 and do 50 pushups you get 8.4 points whereas if you're between 30 and 39 years old and do 50 pushups you get 9.3 points. What I can't figure out is how to get the DLookup when there is more than two columns....although it might have just hit me...

[ssanfu]

I would have 1 table, instead of 5 tables. It would really simplify things. If you have the same setup for females, they should all go into 1 table.
I would be using a record set, not DLOOKUP() to get the points..

What are the fields in your tables?


------------------------
You might check out ELookup() bu Allen Browne.
http://allenbrowne.com/ser-42.html

[June7]

Just echoing Steve again for reinforcement - one table with fields for the age bracket and gender. Include age bracket and gender as criteria in the DLookup or recordset.

[killermonkey]

I put all the pushups into one table with columns for Pushups, M29, M30, M40, M50, M60 where the M## represents males and their age with the data being the points value. What I've found that it's doing now is it's only using the age from the first row in the record and using that to calculate the pushup points for every corresponding record...

Public Function PPnts(PUs) As String  
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("PFT")


   If rst![gender] = "M" Then
      If rst![Age] > 16 And rst![Age] <= 29 Then
         PPnts = DLookup("M29", "PUPoints", "PUSHUPS='" & PUs & "'")
      ElseIf rst![Age] >= 30 And rst![Age] <= 39 Then
         PPnts = DLookup("M30", "PUPoints", "PUSHUPS='" & PUs & "'")
      ElseIf rst![Age] >= 40 And rst![Age] <= 49 Then
         PPnts = DLookup("M40", "PUPoints", "PUSHUPS= '" & PUs & "'")
      ElseIf rst![Age] >= 50 And rst![Age] <= 59 Then
         PPnts = DLookup("M50", "PUPoints", "PUSHUPS= '" & PUs & "'")
      ElseIf rst![Age] >= 60 Then
         PPnts = DLookup("M60", "PUPoints", "PUSHUPS= '" & PUs & "'")
      End If
   End If


   


   'clean up
   rst.Close
   Set rst = Nothing
   Set dbs = Nothing


End Function

[June7]

That's because the code is only addressing the first record of the recordset, which I am guessing is pulled directly from a table and not filtered. Where is this function used? What exactly are you trying to accomplish?

Do you have other tables for other exercises? Is the field structure the same? If so, again, combine to a single table with another field for exercise name or ID.

I suspect if this db were properly normalized, this VBA would not be necessary. If you want to provide for analysis, follow instructions at bottom of my post.

[ssanfu]

By recommending one table, the new structure would be:

PUPoints
------------
PUID_PK
PUSHUPS
Gender (M/F)
Age
Points


But now I am confused by your code.

Why is "PUSHUPS" text? Isn't it the number of pushups? (You have it delimited with quotes)
What is "PFT"? A table, query,...?

What does the data look like in the table "PUPoints"?
Would you give a couple of examples?

Is there a female table?

[killermonkey]

First off let me say thank you for taking the time to help me. Secondly I'll try and explain in a manner that is understandable.

My main table where all my raw data is store is the "PFT" table. This has a persons name, age, gender, weight, height, abdominal circumference (AC), # of pushups performed, # of situps performed and their aerobic time.

What I'm trying to do is make a query that takes all the information from the PFT table and inserts columns for point values on AC, pushups, situps and aerobic time. Initially I did this through an SQL statement that was insanely long and looked something like this:

SUPoints: IIf([PFT].[GENDER]="M" And [PFT].[AGE] Between 10 And 29,(SELECT TOP 1 [POINTS] FROM [29MSITUPS] WHERE PFT.SITUPS = [29MSITUPS].SITUPS),(IIf([PFT].[GENDER]="M" And [PFT].[AGE] Between 30 And 39,(SELECT TOP 1 [POINTS] FROM [30-39M SITUPS] WHERE PFT.SITUPS = [30-39M SITUPS].SITUPS),(IIf([PFT].[GENDER]="M" And [PFT].[AGE] Between 40 And 49,(SELECT TOP 1 [POINTS] FROM [40-49M SITUPS] WHERE PFT.SITUPS = [40-49M SITUPS].SITUPS),(IIf([PFT].[GENDER]="M" And [PFT].[AGE] Between 50 And 59,(SELECT TOP 1 [POINTS] FROM [50-59M SITUPS] WHERE PFT.SITUPS = [50-59M SITUPS].SITUPS),(IIf([PFT].[GENDER]="M" And [PFT].[AGE] Between 60 And 99,(SELECT TOP 1 [POINTS] FROM [60M SITUPS] WHERE PFT.SITUPS = [60M SITUPS].SITUPS))))))))))

The problem I was having with that is that since the SQL was so long I had have two separate queries for males and females. I didn't like this because I need all the data to be in one place.

So I then decided to dive into VBA to create a function to replace the whole mess of SQL code above. I got as far as my original post before I started having serious problems as a result of my non-existent knowledge of VBA. I'm trying to learn as I go but it's getting a little frustrating lol.

This is where I'm at now. I've created a "Pushup" table called PUPoints with both male and female data in it. It looks something like this:

PUSHUPS	M29	M30	F29	F30
46	7.8	9	9.5	10
45	7.7	8.9	9.5	9.5
44	7.5	8.8	9.5	9.5
43	7.3	8.7	9.5	9.5
42	7.2	8.6	9.5	9.5
41	7	8.5	9.4	9.5
40	6.8	8.3	9.3	9.5
39	6.5	8	9.2	9.4
38	6.3	7.8	9.1	9.3

The table goes from 0-100 pushups and obviously includes the other age brackets as well (M40, M50, M60 and F40, F50, F60). The reason I have it set as text is because there are two instances where they don't actually do pushups. They are either exempt (EXP) or Did Not Finish (DNF) which will show EXP or 0 in the points columns respectively.

So again, what I'm trying to accomplish...and failing at is to have a query based off the PFT table data that calculates points based off the PUPoints table.

Again thank you for you help and if I've left any pertinent information out so that you can assist me let me know and I'll be happy to explain further.


Tyler

[killermonkey]

Hopefully this might make it easier. You can pretty much ignore everything except for what is in the "uncategorized" area

Database.zip

[June7]

I tried opening your db in Access 2007 and get 'Unrecognized database format' error.

The pushups field as text can be dealt with or could be a number type and handle the EXP and DNF values with number alias. Assign them a number that no one could ever achieve. EXP could be 998 and DNF could be 999.

This table is still not structured as recommended, however, it could probably be worked with but proper normalization would probably eliminate any need for the function.

Why do you have age and not birthdate? Will there be multiple records for each person? Over years?

[killermonkey]

I built the db in Access 2010, not sure why it won't open in 2007. It did the same thing to my wife when tried to open it in 2007.

I guess I'm confused on how you are suggesting to structure the table. Would you mind providing a sample?

I'm using age because that is the information that is provided to me from the person doing the fitness test. There will be multiple records over time. They will either test once a year or every six months depending on their overall score.