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?

 

[ashwinghanta]

I have tried with your idea of having DLookUp in the footer section. It still is very slow. Even normal Count(*) is very slow. I have about 6000 records in my main table. I made a query(L) out of it which has 4000 records and I have built up other queries around this main query (L). As the load increases on the main table the performance of the form specially(Count) (Max) (Min) (Avg) becomes really slow. How can I avoid this? and the normal Count([]![]) does not work in the textbox. it returns me #Error and Dcount works. What is the reason behind this?

 

[Joe4]

Exactly what kind of Form do you have?
Does it just show one record per screen, or is it a Continuous Form?
What exactly do you use it for?
Why do you have totals on there?

Just trying to figure out if you should maybe be using a Report instead of a Form, but in order to determine that, I need more details.

 

[ashwinghanta]

[/URL][/IMG]

This is how my form looks like. Where we enter dates in the von and bis text fields and click on aktivieren which has me.refresh behind. Then it would fill out the text204 and Maximum, Minimum and Durchschnitt textboxes. The buttons beside text204 would open queries. Now these dates are based on the main Query(L). It has 5 different fields A,B,C,D,E.The field A is a date field. So

Lau.A is between forms!start and forms!end

for the query Summe der Antrage.

WHERE E = 1 AND Lau.A is between forms!start and forms!end

is for query Dunkel.

WHERE E = 0 AND Lau.A is between forms!start and forms!end

is for query Schwebe and Status offen is the difference between Summe der Antrage And (Dunkel+Schwebe).So I need to Count these queries in those text boxes. Count[]![] doesn't work in those text204 either Dcount or Dlookup works. I tried to create another Query which counts these queries. So that I could make it Bound to this form. That doesn't work either. It shows 0 in all text204 fields. Also the Query which Counts all other Queries takes a lot of time to run. How can I simplify this Count so that it runs smoothly?

 

[Joe4]

Without having access to your database, I don't think there is much more I can do.

 

[ashwinghanta]

http://www.filedropper.com/bung

The Query Count has the fields that need to be filled in the textboxes. Although I have cut down the records for easy opening of the file

 

[xenou]

Be sure that your table is correctly indexed (i.e., the search fields or fields used as conditions in where clauses).
In general, try to avoid doing a query twice - if possible. In the extreme, store the results of Min/Max/Avg so you can reuse them as a simple variable rather than having to do the whole query again. In the extreme of the extreme, put everything into a temp table so you can work from a smaller table rather than from the larger main table.

BTW its not clear what you mean by a query that counts all the other queries. Do you mean the sum of all the record counts of all the queries?