Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query for enlisted students during the day (Access 2000)

    How can i count the number of the students enlisted during the last day of the records,provided by the date/time field called EnlistingDay ? My query consists of the fields :
    StudentNumber,lastName,EnlistingDay
    I know already,it should be with DCount,but how?

  2. #2
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for enlisted students during the day (Access 2000)

    Will you please look at my database since i cannot manage it. On my control i see only "name" and not the number of the students for the present day.What is wrong with my form?
    Thank you

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query for enlisted students during the day (Access 2000)

    You will need two functions: DMax to determine the last day, and DCount to get the count:
    <code>
    DCount("*","qrySomething","EnlistingDay=DMax(""Enl istingDay"",""qrySomething"")")
    </code>

    Replace qrySomething with the name of your table/query.
    You can use this in a MsgBox instruction, or in the control source of a text box as =DCount(...)
    Please note the use of double double quotes to include quotes within the WhereCondition argument that is already enclosed in quotes.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query for enlisted students during the day (Access 2000)

    I

  5. #5
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for enlisted students during the day (Access 2000)

    I still cannot do it. It is true, the sign = was missing and i have put it,but i still get "name".May be i have an older version of Windows ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query for enlisted students during the day (Access 2000)

    You inserted a space before the =, remove that space.
    If the list separator on your system is a comma, use
    <code>
    =DCount("*","qrySomething","EnlistingDay=DMax(""En listingDay"",""qrySomething"")")
    </code>
    If the list separator is a semicolon, use
    <code>
    =DCount("*";"qrySomething";"EnlistingDay=DMax(""En listingDay"",""qrySomething"")")
    </code>
    Again, no space before the =.

  7. #7
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for enlisted students during the day (Access 2000)

    Now i have it!. Thank you, Hans !

  8. #8
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query for enlisted students during the day (Access 2000)

    This query works excellent, thank you once again.
    I need to add on a refinement.I need to add on a where condition.In the table students,underlying the query , a have a new field called department.It is a number.Against each student there is the number of the department.For example :WHERE (((TblStudents.department)=1));
    How can i change the DCount formula to include this where condition?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query for enlisted students during the day (Access 2000)

    Try this:
    <code>
    =DCount("*","tblStudents","EnlistingDay=DMax(""Enl istingDay"",""tblStudents"") AND Department=1")
    </code>
    The extra condition has been added to the WhereCondition argument, within the quotes. The word AND specifies that the records must satisfy both conditions.

Posting Permissions

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