Help with DLookup please

[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

 

[Jeff Boyce]

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?

 

[Duane Hookom]

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...

 

[Guest]

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

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

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

 

[Guest]

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

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>

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