Display a number of records in a query on a form

[rocknmoll]

I have a Table (Client_Details) containing all records of customers. Some of these account are open, some are closed.



I also have a query (open_accounts) which just selects accounts with a status of Open.

As accounts are open and closed, I would like to display on my main form a number of current live Open accounts in real time, basically summing the total number of records returned at any given time by the query.

I have inserted a text box on my Form to display, and have tried a number of different ways to have this data display, but keep running into errors or invalid syntax. I have searched everywhere and tried a number of solutions, none of which seem to be working.

Help is much appreciated!

[Minty]

The easiest way would be a DCount() . On your text box try something like;

=DCount("*","YourOpenAccountQueryName")

[rocknmoll]

Thanks. All I get for that is #Name? returned.

[Minty]

Okay, so what have you actually put in the control?
And what is the name of your query ? And to save another reply , what is the SQL of your open account query ?

[rocknmoll]

I have copied your code exactly, save for changing the name of the query to open_accounts, as I specified in my original post.

[rocknmoll]

There is SQL view. I have blacked out customer info.

[Minty]

Okay try specifying the ID ;

=Dcount("ID","open_accounts")

As a bit of advice (before changing things would become a real pain) although Access allows it, starting field or table names with numbers isn't allowed in SQL Server or many other DBMS.
So a table called 1_client_details wouldn't work if you ever upsized to SQL server.

[Micron]

=Dcount("ID","[1_clients_details]","[Account Status] = 'Open'") ?

Agree that names starting with numbers is to be avoided - even more so than spaces or special characters.
I'm surprised the sql would work as there is an instance of the table name that isn't bracketed.

[rocknmoll]

You guys are my heroes! that's sorted it. And thank you for the advice, I will change that. I am quite new to it all, so appreciate the help.

[Micron]

Not sure which worked.
As a self proclaimed newbie, you might want to check out these subjects - if these particular links don't grab your attention, maybe find your own. I think it's the subjects themselves that are the most important.

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[rocknmoll]

Wow, thank you so much. That will be really useful. Again, really appreciate the help.