DCount help please

[fat controller]

I am trying to construct a query with a number of DCounts for a summary report, and each of those counts is to have more than one criteria.

So far, the code below is an example of the code, which returns #Error in five rows

DCount("[ID]","Attendance","[Absent]= True & " And [Date]=" Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]")

I have a feeling that it is the criteria for the date that I have got wrong, but I cannot see the wood for the trees at the moment. Can anyone nudge me in the right direction?

If it makes any difference, all of the DCounts will refer to one table and will all be within the same date range.

 

[pr2-eugin]

Try,

DCount("*","Attendance","[Absent]= True And [Date] Between " & _
        Format([Forms]![DateSelect]![txtStartDate], "\#mm\/dd\/yyyy\#") & " And " & _
        Format([Forms]![DateSelect]![txtEndDate], "\#mm\/dd\/yyyy\#"))

 

[fat controller]

Try,

DCount("*","Attendance","[Absent]= True And [Date] Between " & _
        Format([Forms]![DateSelect]![txtStartDate], "\#mm\/dd\/yyyy\#") & " And " & _
        Format([Forms]![DateSelect]![txtEndDate], "\#mm\/dd\/yyyy\#"))

I get a syntax error from that - "You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks" Access then highlights the second underscore that is prior to the second Format?

EDIT - Removing them seems to keep the peace (as in, no errors) but it does not return a count of the data either

 

[RainLover]

Why do you use DCount.

Instead create a Query with similar criteria as your DCount statement.

Then just count the records.

The Domain functions are there to allow you to search data from a Different source to the one you are working on.

For Example if you are working on a Table about pets, then you want some information from the Owners Table then DCount may be helpful. But to simply count a particular breed of animal within the Pet Table then just use a simple query.

Hope this helps.

BTW your DCount will slow down as the Database grows. It could reach the point where it is unusable because of the lack of speed.

 

[fat controller]

I picked DCount purely because I thought it was what I needed (newbie status showing again , however I am not against using anything else, particularly if it is more efficient.

Would I need a separate query for each of the criteria though?

There are five different criteria in total (all of which are yes/no tick boxes on the form) and I am trying to count the number of each that are true; in query design, if I put 'True' under each field, that would surely only show records where all of those criteria were met?

Am I right in thinking the best bet is five separate queries, five separate sub-reports and on overall report is how it should be done?

 

[RainLover]

I picked DCount purely because I thought it was what I needed.

Am I right in thinking the best bet is five separate queries, five separate sub-reports and on overall report is how it should be done?

I can't comment very much more. I need more details. Is it possible for you to post a cut down version of your Database. I don't need much. just enough to gain a proper understanding. If you could post in 2003 or perhaps 2007, but preferably 2003 then this would also help.

 

[madefemere]

Hi,

And with :
DCount("*","Attendance","[Absent]= True And [Date] Between #" & _ Format([Forms]![DateSelect]![txtStartDate], "mm/dd/yyyy") & "# And #" & _ Format([Forms]![DateSelect]![txtEndDate], "mm/dd/yyyy") & "#")
You got the good result?

 

[Minty]

Based on what you have told us, I would create you counts as separate queries, you can use your forms dates as criteria in all of them. You should then be able to combine all the results into a final query to base you report on.

Alternatively you may be able to use a cross tab query to get all your results in one go, although building reports around cross tabs can be a little "interesting"

 

[sichilaba]

Hi

i got interested to the question you posed.

Just wanted to find out if there is another function equivalent to DCOUNT

Thank you in advance

 

[Galaxiom]

BTW your DCount will slow down as the Database grows. It could reach the point where it is unusable because of the lack of speed.

I don't use them much but my experience is that the domain functions work much like queries. If the condition field is indexed they can be fast.

They generally get a reputation for sluggishness where inexperienced developers use a multitude of them on a form causing it to take a long time to load. Each domain function is like running a separate query.

We don't know enough in this particular case to comment but often the results can be returned with a single query, sometimes with the addition of Count function control sources on controls in the footer.

 

[fat controller]

Thanks for the replies everyone - yet again the proof is in how much clearer things become after some sleep. I 'think' I have it sorted (tried one of the parameters and it worked just fine, but still got to do the rest when I get back on the right PC later); all I have done is to create a small sub-report with the query as the recordsource and it seems to work

 

[sichilaba]

Just came back from Christmas break.

Thank you for the Dcount advice.

To every one Have a great year 2015