Results 1 to 4 of 4
  1. #1
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128

    Formula for week by week data if date range selected by date text boxes

    Hi



    I want to run report where a person selects the date range and say Jan 5th to Jan 30 from date text boxes and could see week by week data for a filter.


    Something like

    Week of Jan 5 Week of 12 Week of 19


    Count of issue = 5 Count of issue = 8 Count of issue = 9


    etc What would be the formula

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Build an aggregate query.

    1. calculate year and week number from the record date field and aggregate data on that criteria
    SELECT Year([datefield]) & Format(DatePart("ww", Date()),"00") AS YrWk, Count(*) AS CountIssue WHERE datefield BETWEEN [start date] AND [end date] GROUP BY Year([datefield]) & Format(DatePart("ww", Date()),"00");

    2. calculate first day of week for each record and group by that

    However, if you want to show detail records as well as summary data, build a report and use its Grouping & Sorting features with aggregate calcs in footer sections. Still do the date manipulation calcs in query (or in the report grouping setup).

    The inputs for [start date] and [end date] can be reference to input controls on a form. But I never use dynamic parameterized queries. I use VBA to build filter criteria and apply to form or report when opening. http://www.allenbrowne.com/ser-62.html
    DoCmd.OpenReport "report name", , , strWHERE
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128
    SELECT Year([RepDate]) & Format(DatePart("ww", Date()),"00") AS YrWk, Count(*) AS CountRootRep
    WHERE RepDate BETWEEN [forms]![QBF_Reports]![start date] AND [forms]![QBF_Reports]![end date]
    GROUP BY Year([RepDate]) & Format(DatePart("ww", Date()),"00");


    I am using this SQL but not Running it's giving error Select statement includes a reserved word or an argument name that is misspelled or missing

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, Date() should have been [datefield] in my example.

    Are you using Design View of query builder or typing in SQL View?

    I don't see anything wrong with syntax. The expressions work for me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-15-2014, 12:23 PM
  2. date range text boxes
    By 4shl3y in forum Forms
    Replies: 5
    Last Post: 07-27-2012, 08:41 PM
  3. Replies: 3
    Last Post: 04-01-2012, 01:40 PM
  4. how to convert week number to date range
    By qwang1115 in forum Access
    Replies: 1
    Last Post: 02-13-2009, 11:35 AM
  5. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 AM

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