Count in Expression Builder in Access

[ashwinghanta]

I have a Query named Data and it has 5 fields A, B ,C ,D, E. The Field A has always distinct values. I have form with where I have an unbound textBox. Through the Expression Builder for the text box. I use

 [/FONT][/COLOR]<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Count([Data!A])</code>

. When I go to the form view it shows me

<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">#Error</code>

.Where as the count in Query Data is 20. Where am I going wrong with the syntax?

 

[Joe4]

Try using the DCOUNT function.
See: https://www.techonthenet.com/access/functions/domain/dcount.php
Note that the criteria argument is optional (and it looks like you won't need it in your case).

 

[ashwinghanta]

DCOUNT makes the form really slow. I want to improve the performance using Count or a VBA code

 

[Joe4]

In which section of your Form are you placing this count?

 

[ashwinghanta]

what do you mean by which section? I placed it in the textbox

 

[Joe4]

Forms and Reports have different sections, such as "Form Header", "Detail", "Form Footer".
What I am getting at, is if you are trying to get a total count, you would NOT put it in the Detail section, or else it will be repeated for every record.
You would want it in a Footer section.

So if you had 1000 records, and you put a DLOOKUP in the Detail section, you would be doing 1000 DLOOKUPs, which would be slow!
If you put it in the Footer, it will only be done once.

 

[ashwinghanta]

Yes, In that case I have it in the "Detail". So you mean if I have DCount or DLOOKUP in the footer section it will be done only once?

 

[Joe4]

Yes.
The Detail section runs once for each record in your Record Source.
The Form Footer section only runs once, at the end, after you have cycled through all the records.

 

[ashwinghanta]

Is it the same principle for buttons which run Queries? Does the position of these buttons matter?

 

[Joe4]

It all depends on the structure of your Form and what these buttons are and what they do.
Typically, most Forms which have buttons to run queries are Unbound Forms (meaning that there is no record source attached to them). So it isn't an issue in that case.
If it was a Form bound to a multi-record Record Source, you would see the button multiple times if it was in the wrong section.
However, buttons don't do anything until you click them anyhow, so they really shouldn't slow anything down.