Results 1 to 8 of 8
  1. #1
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    55

    Count Records in a table

    I have a switchboard form with an unbound textbox on it. I want to make a current statistics overview on this switchboard. I want the count function to count the number of records in a specific table.




    For example I have the following table associated with the following fields.
    "Student Data" is the table name
    "Last Name" is one of the fields.

    I just want a count of how many records are in that table. I keep running into errors when i try to use the expression builder.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    in the ON OPEN or ON LOAD event of the table, assuming the student count field is called fldStudentCount you could do this:

    me.fldstudentcount = dcount("*", "tblStudentData")

    if you want to count all records in tblStudentData (every record should have a last name so you shouldn't need to count last names)

    if you do you can put criteria in the dcount function

    me.fldstudentcount = dcount("*", "tblStudentData", "[StudentLastName] is not null")

    I forget the syntax for null in domain functions but you get the idea.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Actually, it appears that

    "[StudentLastName] is not null"


    and

    "Not IsNull([StudentLastName])"

    both work! Who knew?

    Linq ;0)>

  4. #4
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    55
    I dont have on load or on open in the text box I am trying to put this in. Click image for larger version. 

Name:	sample.jpg 
Views:	27 
Size:	121.7 KB 
ID:	20041

  5. #5
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    55
    Quote Originally Posted by rpeare View Post
    in the ON OPEN or ON LOAD event of the table, assuming the student count field is called fldStudentCount you could do this:

    me.fldstudentcount = dcount("*", "tblStudentData")

    if you want to count all records in tblStudentData (every record should have a last name so you shouldn't need to count last names)

    if you do you can put criteria in the dcount function

    me.fldstudentcount = dcount("*", "tblStudentData", "[StudentLastName] is not null")

    I forget the syntax for null in domain functions but you get the idea.
    I think you meant in the ON LOAD of the form. I got it corrected. Also the syntax didn't want tbl included with it's name, just the name and it worked properly. Thanks for the help...love it when a good plan comes together.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by armyofdux View Post

    ...the syntax didn't want tbl included with it's name, just the name...
    Anytime you use sample code from here, or any forum, you have to substitute your own exact names.

    Linq ;0)>

  7. #7
    armyofdux is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    55
    Is there a way to dcount a range of numbers?

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    If you mean something like 'how many Records are there for Employees with 20 to 40 hours of accrued PTO time (in the example, a Field named APTOT) it would be:'

    =DCount("*", "EmployeeTable", "APTOT Between 20 and 40")

    once again substituting your actual names for EmployeeTable and APTOT.

    Linq ;0)>

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 04-13-2020, 02:13 AM
  2. Replies: 4
    Last Post: 02-04-2014, 10:41 AM
  3. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  4. Replies: 5
    Last Post: 03-23-2012, 11:40 AM
  5. Count records in another table
    By jonnyuk3 in forum Forms
    Replies: 2
    Last Post: 02-23-2011, 04:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums