Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DCount for restricted dates (Access 2000)

    Could someone please let me know what I've done wrong in the formula below. On an Access report, in a text box, we need to include a total count of records under the "Request_Date" field in the record source query that reflects the total number of records requested between January 1, 2007 and December 31, 2007. The record source query also reflects older and later dates, so we only want counted 2007 records.

    I keep getting an error/syntex message when trying to run this formula. I've been away from Access for awhile, so I'm probably not seeing the obvious, and I would greatly appreciate any help I can get. Many thanks!

    DCount("[Request_Date]" > #1-1-2007# and < #12/31/2007#)

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

    Re: DCount for restricted dates (Access 2000)

    The correct syntax is DCount("fieldname","table_or_queryname","condition "), and if you want to use it as the control source of a text box, you must put = before it. So try
    Moreover, you can't state the condition the way you did. You must either repeat the field name:
    <code>
    [Request_Date] > #1/1/2007# AND [Request_Date] < #12/31/2007#
    </code>
    or use Between ... And ...
    <code>
    [Request_Date] Between #1/1/2007# And #12/31/2007#
    </code>
    Note that the latter format includes January 1 and December 31, while the first one excludes them.

    Let's say that the query is named qryRequests. The expression becomes
    <code>
    =DCount("*", "qryRequests", "[Request_Date] Between #1/1/2007# And #12/31/2007#")
    </code>
    Also note that there are quotes around the condition as a whole, not just around the field name in the condition.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount for restricted dates (Access 2000)

    Oh, Hans, thank you so much! I didn't expect such a quick response and at this hour of the morning. Your explanation was very helpful. I copied the expression you gave me, made the change to the query name, and now it works great--just like it's supposed to. You're the best! Many, many thanks!

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

    Re: DCount for restricted dates (Access 2000)

    You're welcome, glad it worked.

    (It's already afternoon for me!) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount for restricted dates (Access 2000)

    Oh, Hans, can you please help me with another expression for the control source of yet another text box on the same report. We want a total figure for the query's "Proposal_Amount" field but only include in that total figure the amounts that fall between January 1, 2007 and December 31, 2007 for the query's "Proposal_Date" field. There are older and later dates in the query. I tried following the DCount expression but it doesn't work for the "Sum" function. Thank you!

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

    Re: DCount for restricted dates (Access 2000)

    Does this do what you want?
    <code>
    =DSum("Proposal_Amount", "qryRequests", "[Proposal_Date] Between #1/1/2007# And #12/31/2007#")
    </code>
    Of course, you must replace qryRequests with the actual name of the query again.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount for restricted dates (Access 2000)

    Thank you, thank you, thank you! I know so little about functions, I'm ashamed to say I didn't know there was a DSum! :-( The expression you sent me works great! I appreciate it very much.

Posting Permissions

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