Results 1 to 8 of 8

Thread: Count function

  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Count function

    I'm a bit of a novice when it comes to access and am trying to set something up where by I have a simple relationship in my database and it already returns a message when you try to duplicate a booking but I would also like to have a message returned when you book a place say how many places are left.

    For example on course one there are 20 place and 15 places have been booked so theres only 5 left so message would read "5 places left" and when course is full I want it to to come up saying full and not let you book any more.

    I think its something to do with a D count and Msgbox but i'm not sure how to word it of where to enter it. I know its a pain but HELP please!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try something like this:
    Code:
    X = DCount("<Column>", "<Tbl_Booking>", "<Criteria>") 
    If X = MaxPlaces then
        MsgBox "All placed are booked."
    Else
        MsgBox MaxPlaces - X & " places left."
    End If
    Where <Column> is any column in the table that stores the booked places (the primary key or any Not Null column would be ideal, but any column can do), <Tbl_Booking> is the table that stores the booked places, and <Criteria> is an optional parameters that restricts the domain of the DCount function. For instance, lets suppose that you store the booked places for different courses into the same table, if you want the count of booked places for course 1 only, <Criteria> would be "Course = 1", with Course being the column where you store the course ID. Maxplaces is, of course, the maximum number of places for a given course.
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    Hi thanks for the reply but this going to sound dum again.

    First is this suppose to go in to a querry if so then which one and where abouts and also if it is a qurry then it keeps asking me for what type of statment?

    Cheers

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is VBA code. It's supposed to be placed in the function you use to enter a new booking is your system.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the dcount function effectively works as a SQL query, I'd guess that internally Access treats the dcount function as a SQL query.

    if you wanted to use the similar COUNT clause in SQL its
    SELECT COUNT(mycolumn) AS NoItems FROM MyTable WHERE acolumn = blah
    the count(mycolumn) maps to Dcounts "<column>"
    NoItems maps to X you can call this anything you like
    MyTable maps to <Tbl_Booking>, or whatever your table/query/view is called
    acolumn=blah maps to the "<Criteria>", you could have multiple criteria eg
    acolumn = blah AND anothercolumn = blah-di-blah

    providing you can express your criteria correctly then either variant works.
    so you are going to be looking at refining your where clause
    to identify the bookings on a specific course and date..... that could be tricky in the dcount function
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2010
    Posts
    3
    so you are going to be looking at refining your where clause
    to identify the bookings on a specific course and date..... that could be tricky in the dcount function
    Is there another way then if its trickey using the dcount function?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the criteria become too complex, you can create a query that acts as a "pre-filter" limiting the domain of the DCount function. You can also create a SQL statement dynamically, open a RecordSet on this statement and use the RecordCount property.
    Have a nice day!

  8. #8
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoCourseA2003.mdb" (attachment, zip).
    Look at Tables, Forms (VBA).
    Open form "frmCourseMain" and try.
    It's an idea only.
    It can be work out.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •