Dlookup function in a report

[Guest]

I've looked at several sources about Dlookup, but I do not have a feel for which entries go where in the Dlookup function. To me it seems that there are more necessary 'parameters' then are required to run the function Dlookup
Here's what I have
My report is named: Grades query_crosstab 2n
Included are fields: English Math , etc
each which has a letter grade - for each individual (record
and this reprot is based on a crosstab query
I am trying to create a GPA so I (believe that I) need the numeric values to find the GPA average
I want to look in the table: Grade poin
with the fields: Grades Gr 4 p
It seems that I need to make at least 4 entries into the Dlookup function, but my reading indicates only 2 (or 3) entries
Could somebody 'easyly' (sorry about that spelling) write what I need to write in the Control Source from the above information. Sorry, but looking at previous posts that seemed to show the similar/same question did not help me (and a lot seemed to be using code)
Note I expect that I will have a text box 'next' to the Enlish grade, and another text box next to the Math letter grade, with very similar paratmeters
I am familiar with Lookup on Excel where the 'parameters'/entries (I'm not sure I'm using the correct terms) are
Lookup (look at what cell, compare with what values, print from what values), where it could say
for cell D2, Lookup (c2, E5:E20, F5:F20) and E5:E20 would show the letter grades and F5:F20 would show the numeric conversion which would make the 'B' in C2 to become 3.0 in cell D2. This is probably a Vlookup function
Any help would be greatly appreciated

 

[Bill Taylor]

John,
The DLookup function in Access works like so: It has three paramiters,
DLookup("FieldToLookIn","TableOrQuery", "Criteria").
Example:

varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")
What DLookup does is find a value stored in a table. It uses string inputs
to determine the field where the value you wish to find is stored, next
param is the table name and lastly is the criteria (like student ID).
So if you needed to find the value of a grade letter for John Smith in the
Grades table where the grade is stored in field Grade then
DLookup("Grade","Grades","[StudentName] =" & "'" & "John Smith" & "'")
HTH
look in vba online help for further assistance.

I've looked at several sources about Dlookup, but I do not have a feel for

which entries go where in the Dlookup function. To me it seems that there
are more necessary 'parameters' then are required to run the function
Dlookup.

Here's what I have:
My report is named: Grades query_crosstab 2nd
Included are fields: English Math , etc.
each which has a letter grade - for each individual (record)
and this reprot is based on a crosstab query.
I am trying to create a GPA so I (believe that I) need the numeric values to find the GPA average.
I want to look in the table: Grade point
with the fields: Grades Gr 4 pt
It seems that I need to make at least 4 entries into the Dlookup function,

but my reading indicates only 2 (or 3) entries.

Could somebody 'easyly' (sorry about that spelling) write what I need to

write in the Control Source from the above information. Sorry, but looking
at previous posts that seemed to show the similar/same question did not help
me (and a lot seemed to be using code).

Note I expect that I will have a text box 'next' to the Enlish grade, and

another text box next to the Math letter grade, with very similar
paratmeters.

I am familiar with Lookup on Excel where the 'parameters'/entries (I'm not

sure I'm using the correct terms) are:

Lookup (look at what cell, compare with what values, print from what values), where it could say:
for cell D2, Lookup (c2, E5:E20, F5:F20) and E5:E20 would show the letter

grades and F5:F20 would show the numeric conversion which would make the 'B'
in C2 to become 3.0 in cell D2. This is probably a Vlookup function.

 

[david epsom dot com dot au]

Dlookup can only lookup ONE value. It can be a calculated value,
(English + Math)/2
across all the fields in one row of the table,

or you can use DSUM and DCOUNT to get an average:
Dsum()/DCount
down all the columns in a recordset, or even the sum of a
calculated value. (You don't have to use a crosstab).


If you use DLookup, DSum or DCount on a report, you can use
fields from the report as part of the criteria in the D(omain) function.
If you use a Domain function in a query, you can use fields from
the query as part of the criteria in the Domain function.

If you want to look up lots of different things, and then calculate the
result later, you have to use the Domain function lots of times

(david)

I've looked at several sources about Dlookup, but I do not have a feel for

which entries go where in the Dlookup function. To me it seems that there
are more necessary 'parameters' then are required to run the function
Dlookup.

Here's what I have:
My report is named: Grades query_crosstab 2nd
Included are fields: English Math , etc.
each which has a letter grade - for each individual (record)
and this reprot is based on a crosstab query.
I am trying to create a GPA so I (believe that I) need the numeric values to find the GPA average.
I want to look in the table: Grade point
with the fields: Grades Gr 4 pt
It seems that I need to make at least 4 entries into the Dlookup function,

but my reading indicates only 2 (or 3) entries.

Could somebody 'easyly' (sorry about that spelling) write what I need to

write in the Control Source from the above information. Sorry, but looking
at previous posts that seemed to show the similar/same question did not help
me (and a lot seemed to be using code).

Note I expect that I will have a text box 'next' to the Enlish grade, and

another text box next to the Math letter grade, with very similar
paratmeters.

I am familiar with Lookup on Excel where the 'parameters'/entries (I'm not

sure I'm using the correct terms) are:

Lookup (look at what cell, compare with what values, print from what values), where it could say:
for cell D2, Lookup (c2, E5:E20, F5:F20) and E5:E20 would show the letter

grades and F5:F20 would show the numeric conversion which would make the 'B'
in C2 to become 3.0 in cell D2. This is probably a Vlookup function.

 

[Guest]

I'm afraid you have totally missed my question
Iwas expecting an answer like
=DLookUp("[GR 4 PT]","GRADE POINT","[GRADES] = " & [Reports]![grades query_crosstab 2nd]![English]
When I used this as my control source, I get #ERROR when I run the preview. I've checked the field and table names as being correct. I've even tried to be case sensitive, even though I don't think it makes any difference.

 

[david epsom dot com dot au]

I still don't understand your question :~)

You want to lookup

DLookUp("[GR 4 PT]","[GRADE POINT]","[GRADES] = 3,5") ??

What is the value of [Reports]![grades query_crosstab 2nd]![English] ???

By the way, on a report you can use the key word 'REPORT' to refer directly
to the active report:

=DLookUp("[GR 4 PT]","[GRADE POINT]","[GRADES] = " & [Report]![English])

(david)

I'm afraid you have totally missed my question.
Iwas expecting an answer like:
=DLookUp("[GR 4 PT]","GRADE POINT","[GRADES] = " & [Reports]![grades query_crosstab 2nd]![English])
When I used this as my control source, I get #ERROR when I run the

preview. I've checked the field and table names as being correct. I've
even tried to be case sensitive, even though I don't think it makes any
difference.

 

[Guest]

[Report]![English] will have each students' English grade, such as A, B+, C-, etc
Then by using DLookup and my Grade Point table I should be able to get the numeric equivalent, 4.0, 3.2, 1.8, etc
I tried your changes, but seem to get the same result
Note: each student will have a grade for each subject

My concern is to correctly write the DLookup statement so that I get the numeric equivalent.

 

[Guest]

The problem seems to be in the criteria part of the DLookup. I take off the criteria and I do get results - not what I want, but results. Always 4.2, which is the first record of the field GR 4 PT in the GRADE POINT table.

 

[david epsom dot com dot au]

No problem!

Because A+ is a text value, you need to enclose it in quotes:

=DLookUp("[GR 4 PT]","[GRADE POINT]","[GRADES] = '" & [Report]![English] &
"'")

(david)

The problem seems to be in the criteria part of the DLookup. I take off

the criteria and I do get results - not what I want, but results. Always
4.2, which is the first record of the field GR 4 PT in the GRADE POINT
table.

 

[Guest]

Thank you
This is what worked
=DLookUp("[GR 4 PT]","[GRADE POINT]","[GRADES] = " & "[Report]![English]"

Now I've run into another 'major' problem. How do I find the average of the English, Math, etc. grades for a GPA. It would be fine if everybody had 7 classes, but some don't.
I have tried 'Average', but the Microsoft Jet database engine does not recognize 'Average' as a valid field name or expression
I tried SUM with the same results
My thoughts of using COUNT would seem to have problems staying within a record.