DLookUp for an unbound control on a form

  • Thread starter Thread starter Linda \(RQ\)
  • Start date Start date
L

Linda \(RQ\)

Hi Everyone,

I have an unbound control on a form in Access 2003. The one DLookUp
expression from a query below works. I added an expression on the same
query and was hoping it would work the same way but I don't know what to do
with the brackets, quotations or commas.

SELECT qryCountOfProtocolTreatments.CountOfPtThpyID,
qryCountOfAllTreatments.CountOfPtThpyID,
[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]
AS ProtocolTreatmentPercent
FROM qryCountOfProtocolTreatments, qryCountOfAllTreatments;


=DLookUp("CountOfPtThpyID","qryCountOfAllTreatments")
The above works perfectly.

=DLookUp("ProtocolTreatmentPercent:
[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]")
The above and an additional 20 or so variations does not work...I need help
on this.

Thanks,
Linda
 
Hi Linda,

Almost there... try this:

=DLookUp("[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]", "QUERYNAME")

Where QUERYNAME is the name of the query where the two subqueries are linked.

Of course, this assumes that these values won't be zero... you might need
to check for that and handle it, but this fixes your dlookup issue.

Another way would be to do it separately like this:
=DLookUp("[CountOfPtThpyID]",
"qryCountOfProtocolTreatments")/dlookup([CountOfPtThpyID]",
"qryCountOfAllTreatments")

Damian.
 
Thanks, Damian,

This is cool stuff! I still don't know what the heck it means but I can
just copy and past qry names and field names to do the same thing in other
projects I have. I have also learned that I can't get a percent expressed
on a query but I just expressed this number as a percent on my form.......So
much to remember!


Linda

Damian S said:
Hi Linda,

Almost there... try this:

=DLookUp("[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]",
"QUERYNAME")

Where QUERYNAME is the name of the query where the two subqueries are
linked.

Of course, this assumes that these values won't be zero... you might need
to check for that and handle it, but this fixes your dlookup issue.

Another way would be to do it separately like this:
=DLookUp("[CountOfPtThpyID]",
"qryCountOfProtocolTreatments")/dlookup([CountOfPtThpyID]",
"qryCountOfAllTreatments")

Damian.

Linda (RQ) said:
Hi Everyone,

I have an unbound control on a form in Access 2003. The one DLookUp
expression from a query below works. I added an expression on the same
query and was hoping it would work the same way but I don't know what to
do
with the brackets, quotations or commas.

SELECT qryCountOfProtocolTreatments.CountOfPtThpyID,
qryCountOfAllTreatments.CountOfPtThpyID,
[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]
AS ProtocolTreatmentPercent
FROM qryCountOfProtocolTreatments, qryCountOfAllTreatments;


=DLookUp("CountOfPtThpyID","qryCountOfAllTreatments")
The above works perfectly.

=DLookUp("ProtocolTreatmentPercent:
[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]")
The above and an additional 20 or so variations does not work...I need
help
on this.

Thanks,
Linda
Click to expand...
Click to expand...
 
Back
Top