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

try use DSum instead? like:

DSum(expr, domain, [criteria])

=DSum("Score","qryScore")

?

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

Thanks

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")

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

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.

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

Nobody wants 500 points?

Try "[Score]" instead of "Score"

How is your form set up?  Would it be possible to use qryScore as the RecordSource?

>> I did that ------> =DLookUp("Score","qryScore","[IndividualID] = " & txtIndividualID)
>>but I am getting  #ERROR

make sure txtIndividualID got a numeric value?

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

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

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!

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! ;-)

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

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 :)

It works great now!

Thanks!