Puzzling DCount behavior

[RagJose]

First post... Not a pro, just a seasoned user. Developed several small Access applications along the years, with extensive use of VBA. Bumped into Access idiosyncrasies many times, but was eventually able to sort them out or work around them. This one, however, has outwitted me, so I'm turning to this forum for help.



I'm putting together a single page (no recordsource) report with stats figures by gender and age range, among other things, for an elders nursing home. I'm using the DCount function to derive each figure. The report presents about 50 different such figures.

Problem is simply: the figures appear correctly for the female population and yield "#Error" for the male part. However, other than the 'F' or 'M' values in the DCount criteria field, the formulas for each set of boxes are exactly the same!

To illustrate what I'm saying:
=DCount ("[estdatsai]", "rp010", "[pesexo]='F' and [fxet]=1") works as expected, while the companion male counterpart
=DCount ("[estdatsai]", "rp010", "[pesexo]='M' and [fxet]=1") gives me #Error.

Visualizing the "rp010" query provides no light. The "pesexo" field contains just M's and F's and there seems to be no other gender-connected data.

Has anyone experienced/solved a similar data-dependent apparent behavior?

[June7]

pesexo is a text type field?

Cannot replicate the issue. If you want to provide project, will look at. Follow instructions at bottom of my post.

[RagJose]

Thanks for the reply, June7. Yes, pesexo is a one-character text field. If you don't mind, I would prefer interacting a bit more before trying to send you the project -- it being field-named and documented in Portuguese will probably be a bit cumbersome to deal with.

New info:
Converted the rp010 query to a table with the same name and structure, causing the DCount calls to operate on it rather than on the query. With that, all DCount results turned out fine. I know Microsoft warns against using parameter queries within DCount and, for the matter, rp010 is a query taking a couple of parameters from controls in an open form. Based on some forum research, I thought this approach would circumvent that restriction, now I'm no longer sure. Anyhow, why should it work ok for some constructs and not for other?

New info 2 (30min thereafter):
Back to the rp010 query, substituted "hard-coded" values in place of the parameters pulled from form controls. Back to the weird #Error results for the male population in the report. So, I guess the query parameters taken from an open form were not the problem.

Can you - or others in this forum - think of other things I might investigate? Or perhaps a totally different approach to accomplish my report?

[June7]

Now I am not clear why you were using DCount (or any domain aggregate function). Show the sql of query rp010. This query is the RecordSource for report? Does report have any grouping & sorting set up?

Maybe instead try Sum in textboxes in header/footer section on report.

= Sum(IIf(pesexo="F" AND fxet=1,1,0))
= Sum(IIf(pesexo="M" AND fxet=1,1,0))

[RagJose]

Thanks again, J7. No, the report has no recordsource, therefore SUM won't work. I'm just putting together kind of a crosstab query with more than 2 dimensions (age range, gender and entering/exiting the institution during a given time period - the latter constituting the variable parameter) for a monthly institutional report.

Here goes the rp010 SQL, with constant values substituted where the parameters came from form fields (and still giving the #Error). It involves four tables. IdAnos is a VBA function that calculates the age in whole years given the birth date (PesNasc) and a reference date. FxEtaria returns the age range (0 to 4) given the age in years. The WHERE clause, besides filtering the date of entry (EstDatEnt) or exit (EstDatSai) that fall within the specified period, require the entry reason (EstMotEnt) and exit reason (EstMudInt) not to mean just a room change, and the accomodation type (UnidTipo, 2-char text) not to be "--", "Ch" or "Fc". Everything works as expected, except for the male population ("M" stored in PeSexo).

SELECT
T1Pessoas.PeSexo,
FxEtaria(IdAnos([PesNasc],#3/31/2012#)) AS FxEt,
T3Estadias.EstDatSai,
IIf(IsNull([estdatsai]),"Entrou",Null) AS AuxEntr
FROM
(T6Unidades INNER JOIN (T1Pessoas INNER JOIN T3Estadias ON T1Pessoas.PesID = T3Estadias.EstPtPes)
ON T6Unidades.UnID = T3Estadias.EstPtUnid) INNER JOIN T2CamposInt ON T1Pessoas.PesID = T2CamposInt.IntPtPes
WHERE
(((T3Estadias.EstDatEnt)<=#3/31/2012# And (T3Estadias.EstDatEnt)>#2/29/2012#) AND ((T3Estadias.EstMotEnt)>1) AND ((InStr([UnidTipo],"--ChFc"))=0)) OR
(((T3Estadias.EstDatSai)<=#3/31/2012# And (T3Estadias.EstDatSai)>#2/29/2012#) AND ((T3Estadias.EstMudInt)=False) AND ((InStr([UnidTipo],"-ChFc"))=0));

To count the people exiting the institution in the reference period, I use slightly different DCount constructs, which yield the same #Error for males.

=DCount ("[auxentr]", "rp010", "[pesexo]='F' and [fxet]=1") (works ok)
=DCount ("[auxentr]", "rp010", "[pesexo]='M' and [fxet]=1") (gives #Error)

In both situations (entry and exit) I'm just counting non-null occurrences of the entry and exit dates. I'm increasingly suspicious of a Jet engine bug.

If an insight into the problem does not show up shortly, I will look into your suggestion of using SUM. The drawback is that I will have to split the report into pieces (other sections currently working are based on DCounts using different queries).

[June7]

Sorry, don't see anything in the query to cause this issue. Seems everything is in order for the DCount to work. At this point, only thing else I could do is review project.

[RagJose]

Thanks indeed for your willingness to help. I will look into making a project replica I can send you. Removing confidential data (basically person's names and addresses) is a bit tricky but maybe I can figure out a way to scramble them. Hope to do it tomorrow. Cheers!

[RagJose]

Found the origin of the problem. Amidst the hundreds of records in the database, there was one male person with a Null birthdate. My code for calculating the age in years, used in one of the columns of query RP010, was not prepared for a null argument. Fixed just that, and all DCount figures in my report popped "back to life".

What I fail to understand is, that particular record was filtered out by the query, thanks to other criteria, in all tests I conducted. How could DCount be affected by it? Still interested in this answer, if someone has it -- will wait a while before flagging this thread "solved".

June7, again a big thanks for your time and availability. Glad you didn't have to dig into the project.

-- José.

[June7]

Somewhat of a guess, but would appear that even though the record filters out, the expression must still evaluate to a valid result or subsequent calcs using that constructed field will also fail.