DLookUp function and Queries

[SharonC]

I feel like this could be a single quote/double quote problem or maybe I'm asking too much out of a single query, but here goes.

I have a table (called Cadet Data File) that has several fields, 4 of which are: CurlUpPoints, RunTimePoints, PushUpPoints. All numeric (integer). Then there's Sex (male/female).

In a query called Top Physical Fitness Scores by Cadet I've created a calulated field named Physical Fitness Points which totals [CurlupPoints]+[RuntimePoints]+[PushupPoints]. Sex is also being listed. See first.jpg.

I have a another table called PTPointTable. It has 3 fields named MaleGrade, FemaleGrade, PTTotalPTs. See second.jpg.

I want to add another calculated field to this query which would state:

PTPoints: = IIF([Sex] = "Male",DLookUp("MaleGrade","PTPointTable","PTTotalPts = [Physical Fitness Points]", DLookUp("FemaleGrade","PTPointTable","PTTotalPts = [Physical Fitness Points]"))

When I run this query it keeps telling me it can't "find" [Physical Fitness Points].

Could this be because the query is in the process of calculating the 3 fields, Curlups, Runtime, Pushups?

I'm pretty sure that I have the DLookUp function typed correctly as I've used this formula in various forms and reports. It just doesn't want to cooperate in the query.

I've also tried basing a query upon the query, and that didn't work either.

May the gods above bless each and everyone of you who has the tenacity to sit and figure out all these "simpleton" type procedures for us "nuggets". Rest assure, you're paving your way to heaven.

 

[Pat Hartman]

The syntax for the DLookup() is incorrect. Try this:

DLookUp("MaleGrade","PTPointTable","PTTotalPts = " & [Physical Fitness Points]

BTW, a FAR better solution is to normalize your PTPointTable so that you have a separate row for the male/female numbers rather than a separate column. That way you can get rid of the DLookup()s entirely and just add the PTPointTable to the query and join it to the main table on Sex and PTTotalPts.

 

[SharonC]

Thank you, Mr. Hartman....your suggestion worked like a charm!

I also give your suggestion to the PTPointTable further consideration. There's no doubt I'm going "around my elbow to get to my thumb"

Cheers!

 

[Mile-O]

Thank you, Mr. Hartman....your suggestion worked like a charm!

Mrs!