DCount and referencing controls in an Access project report

[karatelung]

this works in my Access (.mdb) report as the control source for an unbound text box:

=DCount("*","[qryCurrentClients]","[NewResidence] = [Report]![rptAvailability].[Identification]")

...but it doesn't work in the Access project (.adp) environment. i've narrowed it down to the way I'm referencing the [Identification] control because if I just throw in a valid Identification number like this:

=DCount("*","[vwCurrentClients]","[NewResidence] = 105")

it'll give me the count for that specific [Identification].

Does anyone know how to correct this for use with reports in an Access Project? I already searched this board and couldn't find it. I'm sure it's here somewhere, but searches don't always go as planned.

Thank you.

Richie

 

[KenHigg]

Not sure how you got this to work:

=DCount("*","[qryCurrentClients]","[NewResidence] = [Report]![rptAvailability].[Identification]")

It should be something like:

=DCount("*","[qryCurrentClients]","[NewResidence] = " & [Report]![rptAvailability].[Identification] & )

or:

=DCount("*","[qryCurrentClients]","[NewResidence] = '" & [Report]![rptAvailability].[Identification] &"'")

???
kh

 

[karatelung]

Thanks, but I couldn't get either of those work.

just to make sure I'm not crazy, I went back to the mdb file where it worked, and it was just like the first example in my post. i remember just playing around with the syntax of that until it worked.

there's got to be a way to make it work in an adp file. i just need to count every record where [NewResidence] in the view vwCurrentClients = [Identification] in the open report on which the text box resides (rptAvailability).

Any other syntax suggestions to make this work.

 

[karatelung]

pat, thanks for pointing out my inefficient design. i took your advice, and it works beautifully and SO much faster.

richie

 

[karatelung]

okay, the only problem i'm facing now is this:

The source for the report is a query that joins [tblFosterParents] to [vwAvailCount].

The calculated field from the query that I use on the report is [Filled]. I have a field on the report [Available] and its source is =[Capacity]-[Filled].

The problem is that I no longer have 0's. I have my join in the query so that all records in [FosterParents] show. That way it doesn't have to match a record in [vwAvailCount], the query with the calculated field.

When dealing with one of those records on the report, the [Filled] field is just blank instead of zero. This means that [Available] is also blank for those records. I have conditional formatting based on [Available].

How do I get [Filled] to be zero instead of blank on the report?

Thanks

 

[karatelung]

Thank you. That gave me the idea to take care of it at the query level:

Coalesce([Filled], 0) AS Filled

Nz isn't supported in .adp's. I found that out about a week ago.

Richie