• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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
0
lenyon73
Asked:
lenyon73
  • 8
  • 6
  • 3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Ryan ChongCommented:
>>txtScore.Value = "" & DLookup("Score", "qryScore", "[IndividualID] = '" & txtIndividualID & "'" And "[SnapshotDate] = #" & txtSnapshotDate & "#")
?

try:

txtScore.Value = "" & DLookup("Score", "qryScore", "[IndividualID] = '" & txtIndividualID & "' And [SnapshotDate] = #" & txtSnapshotDate & "#")
0
 
lenyon73Author Commented:
It works great now!

Thanks!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now