Using DLookup to get calculated value from a query to a form field

Hi,

I have a query that calculates a score and I want to show that value in a form textbox.
I used the Dlookup function on the control source of the textbox ------>   =DLookUp("Score","qryScore")
My problem is that the textbox now shows only the first result of the query for all the records.
How can i fix that?
That's 500 easy points! :)

Thanks
lenyon73Asked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
>>txtScore.Value = "" & DLookup("Score", "qryScore", "[IndividualID] = '" & txtIndividualID & "'" And "[SnapshotDate] = #" & txtSnapshotDate & "#")
?

try:

txtScore.Value = "" & DLookup("Score", "qryScore", "[IndividualID] = '" & txtIndividualID & "' And [SnapshotDate] = #" & txtSnapshotDate & "#")
0
 
Ryan ChongCommented:
try use DSum instead? like:

DSum(expr, domain, [criteria])

=DSum("Score","qryScore")

?
0
 
lenyon73Author Commented:
That gives me a score of 128% instead of 63% and it also shows the same result for all the records.

Thanks
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
JohnK813Commented:
Are you maybe looking then to average all the scores together?  For instance, if I had two tests (or whatever), and got a 50% on each, my total score would be a 50%, not 100%.  In that case:

DAvg("Score","qryScore")
0
 
lenyon73Author Commented:
The score is already calculated as an average in the query. I have many people records though, so everyone has a different average score. So I want to see each person's average (the one that was calculated in the query).
If I use DAvg i will get the average of the averages! Does that make sense?

Thanks
0
 
JohnK813Commented:
In that case, you'll need to use the criteria part of Dlookup, something like:

=DLookup("Score","qryScore", "[PersonID] = " & txtPersonID)

Basically, the criteria part of DLookup is the same as a WHERE clause in a SQL statement.  In this example, qryScore has two fields: Score and PersonID.  txtPersonID is another textbox on your form.  Here, I'm telling DLookup to give me the Score that matches the PersonID currently displayed in txtPersonID.
0
 
lenyon73Author Commented:
I did that ------> =DLookUp("Score","qryScore","[IndividualID] = " & txtIndividualID)
but I am getting  #ERROR

Then i tried this -------> =DLookup("Score","qryScore","[IndividualID] = 1") which works in the query
but i am also getting #ERROR

Am I doing something wrong?

Thanks
0
 
lenyon73Author Commented:
Nobody wants 500 points?
0
 
JohnK813Commented:
Try "[Score]" instead of "Score"

How is your form set up?  Would it be possible to use qryScore as the RecordSource?
0
 
Ryan ChongCommented:
>> I did that ------> =DLookUp("Score","qryScore","[IndividualID] = " & txtIndividualID)
>>but I am getting  #ERROR

make sure txtIndividualID got a numeric value?
0
 
lenyon73Author Commented:
txtIndividualiD has a numeric value.
I have another table used as the form RecordSource (tblQLSR). Is it possible to add my query there too? (something like (tblQLSR + qryScore or tblQLSR + qryScore.Score) ?

Thanks
0
 
Ryan ChongCommented:
Hmm... sorry, not very sure on tblQLSR + qryScore or tblQLSR + qryScore.Score ...

but seems like you can do some VBA programming (DAO, for example) to get the calculate result and display it into your textbox.

try post your tables' fields design, along with your criteria on what you try to query out here?

regards
0
 
lenyon73Author Commented:
My database is available for download at http://s14.yousendit.com/d.aspx?id=0NJAI1VGMH1Z0YU6I20FVL5PW

The form that i am trying show the query calculated field is called fsubQLSR and the query is called qryScore.

If you can take a look at it and let me know what I am doing wrong that would be great.

Thanks!
0
 
Ryan ChongCommented:
I changed a bit to your Access application:

1. Changed query qryScore to:

SELECT tblIndividual.IndividualID, tblIndividual.FirstName, tblIndividual.LastName, tblQLSR.SnapshotDate, tblQLSR.PlanOfCareDate, ([Individuality]+[Integration]+[Relationships]+[Security/Stability]+[Choice/Preference]+[Opportunity]+[Dignity/Status]+[Self-Advocacy]+[Independence]+[Joy])/10 AS Score
FROM tblIndividual INNER JOIN tblQLSR ON tblIndividual.IndividualID = tblQLSR.IndividualID;

2. Edit form fsubQLSR, make textbox txtScore to become an Ubound textbox.

3. Add:

txtScore.Value = "" & DSum("Score", "qryScore", "[IndividualID] = '" & txtIndividualID & "'")

at the end of Sub Form_Current

If not wrong, is this what you want to do?

btw, your form looks good and cute! ;-)
0
 
Ryan ChongCommented:
and yes, request for an amended copy of your Access application from me if necessary, fyi, you can found my Personal Email address at my profile.

cheers
0
 
lenyon73Author Commented:
It almost works now!

I tried to find the solution without bothering you again but I couldn't.

The problem now is that even though i have 4 people with 2 records each (8 records) I can see only the information for their first record (4 records). I realized that I need one more criteria (which is the SnapshotDate) in order to make this work correct.
I tried to use:

txtScore.Value = "" & DLookup("Score", "qryScore", "[IndividualID] = '" & txtIndividualID & "'" And "[SnapshotDate] = #" & txtSnapshotDate & "#")

and it does not work ( I get a type mismatch runtime error).

My guess is that it does not work because txtSnapshotDate had an input mask (99/99/0000;0;_)

You think I can change something and make it work?

Thanks again!

P.S I need to use DLookup and not DSum :)
0
 
lenyon73Author Commented:
It works great now!

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.