Help with DLookup please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm using the following in an unbound text box in a report and it always
produces a result of 1. What am I doing wrong?

It needs to get the value of field TotalPlacements (produced using Count in
the query) from qrySECCountOfPlacements, where the SchoolID (numeric) matches
the record being shown and where field Subject (Text) contains Citizenship.


=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) And "[Subject]='Citizenship'")

Thanks, JohnB
 
John

May not be relevant, but it looks like your expression is saying something
like:

where [SchoolID] = [SchoolID]

I'm confused, and maybe Access is too. Where is the second [SchoolID]
coming from, and can you give it a different name?
 
The "And" must be inside the quotes. You need to use something like:
=DLookUp("TotalPlacements", "qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) & " And [Subject]='Citizenship'")

Watch for line wrapping...
 
Excellent Duane. Works a treat - these DLookup and DCount functions drive me
up the wall. Thanks, JohnB

Duane Hookom said:
The "And" must be inside the quotes. You need to use something like:
=DLookUp("TotalPlacements", "qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) & " And [Subject]='Citizenship'")

Watch for line wrapping...

--
Duane Hookom
MS Access MVP
--

JohnB said:
Hi. I'm using the following in an unbound text box in a report and it
always
produces a result of 1. What am I doing wrong?

It needs to get the value of field TotalPlacements (produced using Count
in
the query) from qrySECCountOfPlacements, where the SchoolID (numeric)
matches
the record being shown and where field Subject (Text) contains
Citizenship.


=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) And "[Subject]='Citizenship'")

Thanks, JohnB
Click to expand...
Click to expand...
 
Thanks Jeff.

Duanes post has solved the problem for me. The SchoolID code matches the
SchoolID in the queries record set with that shown on each line in the report.

Thanks for the reply. JohnB

Jeff Boyce said:
John

May not be relevant, but it looks like your expression is saying something
like:

where [SchoolID] = [SchoolID]

I'm confused, and maybe Access is too. Where is the second [SchoolID]
coming from, and can you give it a different name?

--
Regards

Jeff Boyce
<Office/Access MVP>

JohnB said:
Hi. I'm using the following in an unbound text box in a report and it always
produces a result of 1. What am I doing wrong?

It needs to get the value of field TotalPlacements (produced using Count in
the query) from qrySECCountOfPlacements, where the SchoolID (numeric) matches
the record being shown and where field Subject (Text) contains Citizenship.


=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) And "[Subject]='Citizenship'")

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