Results 1 to 10 of 10
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Can DCount be used to calculate month to date?

    I've got a query that is filtering down to year to date entries and then I'm using a DCount function in a report to show that number. I'd also like it to show the current month-to-date number as well. Outside of creating a whole query, is it possible for the DCount function to be coded in a way that just generates the MTD number based on the YTD query?

    My current DCount function is simple and my date field is submissiondate:

    Code:
    =DCount("*","qryYTD")


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you make a form that has both : txtStartDate and txtEndDate
    the query would pull data in this range: select * from table where [dateFld] between forms!fMyForm!txtSTartDAte and forms!fMyForm!txtEndDAte

    then you can set your range to whatever , MONTHY, YEARLY,
    then the Dcount can count whatever is in the query.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    really depends on what your qryTYD returns but perhaps something like

    =dcount("*","qryYTD","format(submissiondate,'yymm' )='" & format(date,"yymm") & "'")

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by Ajax View Post
    really depends on what your qryTYD returns but perhaps something like

    =dcount("*","qryYTD","format(submissiondate,'yymm' )='" & format(date,"yymm") & "'")
    What date field is supposed to go after the second 'format'?

    I've attached an example DB if you'd like to take a look at my formatExample1.accdb

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    date is a function that returned todays date - but just noticed a typo - should be date()

    Just looked at your table - date should be a date field, not text and customerid should be a number

  6. #6
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    That worked!! Any idea how to alter it to do just the past 7 days?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use the criteria

    "submissiondate between date()-7 and date()"

    or

    "submissiondate>= date()-7 and submissiondate<=date()"

    but this will only work if you change the field type to date

    for decent performance it should also be indexed

  8. #8
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by Ajax View Post
    use the criteria

    "submissiondate between date()-7 and date()"

    or

    "submissiondate>= date()-7 and submissiondate<=date()"

    but this will only work if you change the field type to date

    for decent performance it should also be indexed
    So like this?

    Code:
    =DCount("*","qryYTD","[SubmissionDate] Between Date()-7 And Date()" & "'")

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no - as I provided, you are dealing with dates so you don't need

    & "'"
    again, on the proviso you change the datatype to date. I wouldn't even want to suggest what you would need as a formula if you leave it as text

  10. #10
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    That worked! Thank you!

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

Similar Threads

  1. Calculate YTD and Rolling-12 Month Totals
    By McArthurGDM in forum Queries
    Replies: 2
    Last Post: 04-07-2015, 06:36 PM
  2. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  3. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  4. Replies: 4
    Last Post: 07-30-2012, 11:55 AM
  5. Replies: 4
    Last Post: 10-27-2010, 02:57 PM

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