Count number of records by date

Discussion in 'Access General' started by Brendon, Sep 23, 2013.

  1. Brendon

    Brendon Guest

    I apologize if I'm not asking correctly, but I can't find my question by searching the group..

    I have a text field on a form. What I want to accomplish is a count of records for the current day for the value of the text field.

    here's what I have dcount("employee","tblEMPLOYEE",[date]=Now()

    but it keeps giving me an error.

    Any help would be appreciated.
     
    Brendon, Sep 23, 2013
    #1
    1. Advertisements

  2. First off... you don't have a text field on a Form. You have a CONTROL on a
    form, probably bound to a text field, in a Table. Data is stored in tables,
    and only in tables; forms are just tools, windows to display data stored in
    tables.

    Secondly, count your parentheses. They need to balance and they don't; you
    have two left parens and one right.

    Third, all three arguments to DCount must be text strings. The optional third
    argument must be a text string which evaluates to a legal SQL WHERE clause
    without the word WHERE.

    Fourth, the Now() function doesn't return today's date; it returns the current
    time accurate to a few microseconds. There certainly won't be any records in
    tbloEmployee with this precise instant.

    Fifth, your table field might or might not have a time component. All
    date/time values have both a date and a time - they're stored as a double
    float number, a count of days since a start point; a fractional component of 0
    means midnight.

    If [Date] contains just a date, with a midnight time, try

    DCount("*", "tblEmployee", "[Date] = #" & Date() & "#")

    If it contains both a date and time, i.e. if the table is also populated using
    the Now() function, use

    DCount("*", "tblEmployee", "[Date] >= #" & Date() & "# AND [Date] < #" &
    Date() + 1 & "#")

    Finally - Date is a reserved word for the builtin Date() function; as such
    it's a bad choice as a fieldname, since Access can get confused about whether
    you mean the fieldname or the function.

    Note also that this newsgroup is no longer supported by Microsoft, and very
    few people answer questions here (I just post from old habit). See my .sig for
    more up to date alternatives.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 23, 2013
    #2
    1. Advertisements

  3. Brendon

    Brendon Guest

    Thank You John, this worked, I will look to the other forums going forward.Thank you for your assistance.
     
    Brendon, Sep 23, 2013
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.