DCount criteria based on report detail

[Mr Smin]

Hi,

I can't figure out what's wrong with this Dcount, which is the source for a text box in a report detail section - It just returns a zero for each detail row.

=DCount("[ClientID]","tblPlace",("[Date] Between #" & (Date()-31) & "# And #" & Date() & "# )") And ("[SiteID]"="  & Me!Text55 &  "))

Text55 is another textbox in the report detail which contains a SiteID number.

If I take out the SiteID part of the criteria then a sensible (but not correct) value is returned so I don't think the issue is with the dates part of the criteria.

Can anyone explain what I've missed?

Thanks.

 

[vbaInet]

Try this:

=DCount("[ClientID]","tblPlace","[SiteID] = "  & Me!Text55 & " AND [Date] Between " & (Date()-31) & " AND " & Date())

I would advise against against using DATE as a field name.

 

[Mr Smin]

Thanks for the reply.

I tried it and Access prompts for Me as a parameter. I have tried entering an ID number or leaving it blank - in either case the textbox shows #error on the report.

This is quite hair-tearing!

ps. I changed the field name to Date just for the purposes of my post - which caused more confusion than it saved.

 

[vbaInet]

That's correct, refer to the textbox without Me! or use the field name

=DCount("[ClientID]","tblPlace","[SiteID] = "  & [COLOR=Red][B][FieldName][/B][/COLOR] & " AND [Date] Between " & (Date()-31) & " AND " & Date())

OR

=DCount("[ClientID]","tblPlace","[SiteID] = "  & [COLOR=Red][B][text55][/B][/COLOR] & " AND [Date] Between " & (Date()-31) & " AND " & Date())

 

[Mr Smin]

vbaInet, that's now giving me some value for each row rather than an #error or zero for each row, but the values don't seem to match anything in the underlying table!


This is some SQL that returns correct results.

SELECT tblPlace.PlaceID, tblPlace.SiteID, tblPlace.EndDate
FROM tblPlace
WHERE (((tblPlace.EndDate)>(Date()-31) And (tblPlace.EndDate)<Date()));

I can’t see why I am getting different results from the DCount – the criteria look the same to me.
I wondered if it was because the ClientID is not unique in this table so I switched to counting on the primary key, but that produces the _same_ incorrect results.
I also excluded the possibility that dates null/not null were being counted.

[/FONT][/COLOR]
[COLOR=black][FONT=Arial]=DCount("[PlaceID]","tblPlace","[SiteID]=" & [Text55] & " AND [EndDate] Between #" & (Date()-31) & "# And #" & Date() & "#")[/FONT][/COLOR]
[COLOR=black][FONT=Arial]

I’m hoping my error will be obvious to you!

 

[vbaInet]

Include the PlaceID in the criteria as well using AND.

 

[Mr Smin]

I'm not sure what you mean? There are no criteria for the PlaceID in this context, I want to return all of them, subject to the other criteria.

I have a workaround now, which seems less readable/efficient. A DCount in the report calls a query (like the one I posted) which in turn reads the SiteID from the report as a criteria.

 

[vbaInet]

In that case get rid of the hash "#" characters surrounding the Dates. They are probably returning incorrect dates.

Good thinking, but double work. Consider using a subform if the above fails. Or use a hidden combo box (setting it's row source to the sql string, restricting it by SiteID) and point to it's value from the textbox.