DCount and referencing controls in an Access project report (1 Viewer)

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

 

[Pat Hartman]

I don't know why this won't work in an .adp (as I have said many times. I don't use .adp's). I do know that this is an inefficient way of accomplishing the task. Using the DCount() requires Access to run a separate query for each row in the report. Apparently you don't have a large recordset or you'd be here asking why this report was sooooooo slooooow.

Proper technique is to create a totals query and in the report's RecordSource query, join to the new totals query. That will give you the count as part of the Report's RecordSource so you won't need the DCount() at all. You'll just be able to bind the count field directly.

 

[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

 

[Pat Hartman]

When you perform calculations with numeric fields which may contain dulls, you need to get rid of the nulls.

= Nz([Capacity],0) - Nz([Filled],0)

Aggregate and domain functions ignore nulls but expressions do not.

 

[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