Results 1 to 4 of 4
  1. #1
    BGNR Loel is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Posts
    2

    Thumbs up Help on Report formatting on query with start and end dates

    Hi, I'm new to the forms and this is my first post. I've searched for answers and some where very helpful but not exactly what I'm trying to do. I'm a beginner in access and just know the basics but its all basic logical thinking. Thanks in advance for any help and suggestions.

    In short, I have a simple tblData (shown below) with a start and end dates. I have a qryData (SQL shown below) pulling in all the information from the tblData with the filtering on the start and end dates thru a form. The form, frmMain has two text boxes (start date and end date). I also have a Run command button with a macro to open my report rptData. I'm able to filter the data using my frmMain form but I wanted to get the results shown in the expected results section. I've seen this done thru code but could not find any answers on the web.

    Below is my access data with one table, one query, one form, and one report
    ----------------------------------
    Table: tblData


    Click image for larger version. 

Name:	tblData.jpg 
Views:	18 
Size:	39.2 KB 
ID:	33937
    ----------------------------------
    Query: qryData
    Code:
    SELECT tblData.[Name ID], tblData.Name, tblData.[Action ID], tblData.Action, tblData.[Start Date], tblData.[End Date]
    FROM tblData
    WHERE (((tblData.[Start Date])>=[Forms]![frmMain]![tbStart]) AND ((tblData.[End Date])<=[Forms]![frmMain]![tbEnd]));
    ----------------------------------
    Form: frmMain

    I have two text boxes, one is the start date and the second is the end date. I also have a command button "cmdRun" which I'm triggering a macro to open up the report rptData.

    ----------------------------------
    Report: rptData

    1. Results using start and end date filters from form frmMain with dates 1/1/2018 to 1/31/2018
    Click image for larger version. 

Name:	filtered.jpg 
Views:	18 
Size:	27.2 KB 
ID:	33939

    2. Unfiltered results showing all query data
    Click image for larger version. 

Name:	unfiltered.jpg 
Views:	18 
Size:	39.1 KB 
ID:	33940

    3. Expected results I'm trying to achieve using the same start and end dates 1/1/2018 to 1/31/2018 on form frmMain. I expect the code to be more complex here to get these results using the same query data.
    Click image for larger version. 

Name:	expected.jpg 
Views:	18 
Size:	39.7 KB 
ID:	33941

    ----------------------------------
    My actual test database with the above information using access 2007
    http://www.lowfrz.com/downloads/TestData.zip

    ----------------------------------
    Thanks for the suggestions and helpful information. If anyone knows of any videos to look at that are out there will also be helpful.


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you saying that you want to include records that overlap the start/end dates but only include time within the period? This should help:

    http://www.baldyweb.com/OverLap.htm

    And this to return the appropriate value:

    http://allenbrowne.com/func-09.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm thinking your problem is due to the time factor. Not sure because you're leaving us to decipher what's wrong rather than pointing it out. It seems to me that you're missing records that you expect to get between certain start and end dates, and this would be because when you only provide a date as the end date against a field that contains time, the time ends at 12:00:00 (midnight on that date) when you don't provide the time. In your case, you cannot add the time because it's unknown, thus you don't get 01/31/2018 01:00:00 (1 A.M.) when your end date is 01/31/2018.

    You'd need to utilize 1 of several workarounds:
    - go a day beyond the desired day in every search (not my favourite)
    - directly add time to your date (as in + x hours or + x minutes)
    - use the DateAdd function to add x minutes to your date
    The amount of time you add would be up to you. If it's a 24 hour operation, you'd need to add 1440 minutes
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    BGNR Loel is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Posts
    2
    Thanks pbaldy, these links are helpful and it is what I'm looking for. I'll have to try them out when I get home today. Yes, I want to include time within the period. Eventually I want to run reports by month to show total hours within the month and at the same time I want to avoid necessary data entry if their consecutive hours overlap between months. I can have them enter one entry instead of two, to separate the months.

    Micron, thanks for your feedback as well as you also provided some very useful alternatives. I may be able to achieve the results I need by creating another query off of the first query with some date logic to add or remove time based if a date range is between two months. I can then run the report of this new query.

    Thanks for the help everyone.

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

Similar Threads

  1. Start and End Dates must appear in the Report
    By trident in forum Reports
    Replies: 3
    Last Post: 09-06-2017, 09:37 AM
  2. Replies: 6
    Last Post: 02-06-2015, 12:45 PM
  3. Replies: 6
    Last Post: 06-28-2014, 07:45 PM
  4. Replies: 1
    Last Post: 12-07-2012, 10:02 AM
  5. Replies: 3
    Last Post: 04-11-2012, 01:13 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