Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unhappy Unanswered: Dcount utilizing Start/End Date in Report

    Hi all. new to scripting.

    I need to run a report that when prompted for the Start/End date my formula (below) ,embed in the text box, will accept the Start/End date as range and return the %. this formula works well, as is, w/o the Start/End parameters.

    here is the formula:

    =(DCount("[dateAnsw]","MonthlyHostSiteCheckIn Query","[Sup_Availability] = 'Yes'"))/(Count([date]))

    this is based on a survey prompting for YES/No.

    I have tried changing it to this - -

    =(DCount("[dateAnsw]","MonthlyHostSiteCheckIn Query","[Sup_Availability] = 'Yes'") where DateAnsw between [Start date] and [End date])/(Count([date]))

    ...but it doesn't work.

    helllppp.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would try:

    =(DCount("[dateAnsw]","MonthlyHostSiteCheckIn Query","[Sup_Availability] = 'Yes'") where DateAnsw between #" & [Start date] & "# and #" & [End date] & "#)/(Count([date]))

    or

    =(DCount("[dateAnsw]","MonthlyHostSiteCheckIn Query","[Sup_Availability] = 'Yes'") where DateAnsw between cdate([Start date]) and cdate([End date]))/(Count([date]))

    The problem with this approach is that the user can enter whatever they want like "Hi" which will produce an error. I usually create a form (RepSel) that will get the needed parameters like start and end dates then the user has to click a button that will check the data and run the report. So:

    =(DCount("[dateAnsw]","MonthlyHostSiteCheckIn Query","[Sup_Availability] = 'Yes'") where DateAnsw between [forms]![RepSel]![Start date] and [forms]![RepSel]![End date])/(Count([date]))

Posting Permissions

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