Results 1 to 12 of 12
  1. #1
    tlyon is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    8

    Daily Report

    Just trying to make a daily report from my database. Just lets me do ranges. How do I run just one day.

  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
    Why just lets you do ranges? What is the query the report is based on?
    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
    tlyon is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    8
    I am tracking Downtime and want a daily report from this table.

    Click image for larger version. 

Name:	Picture1.jpg 
Views:	17 
Size:	161.5 KB 
ID:	30706

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Depends. Is the report based on a table or query? It should be a query. Then you have criteria in a WHERE clause, like

    WHERE SomeDateYouSupply = [DATA SHEET].[PRODUCTION DATE]
    as long as the production date field doesn't contain time part of the date. If it does, you will probably have to tweak this.

    If not, you will have to add a filter to the report.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    tlyon is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    8
    Yes I am running a query from the table - but I am unsure how to make sure it is looking at just the date and not date and time. I believe it must be looking at time because if i try and run a report for yesterday nothing populates and there is data there. How do I correct this? I am using this criteria. Between [Forms]![Date Picker]![txtBeginProductionDate] And [Forms]![Date Picker]![txtEndProductionDate]

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Start by looking at the table in design view. Is the date field Date/Time data type, and if so, what is the format? Is there a default value specified, such as Now()?
    No point in delving into how to fix a problem that may not exist; i.e. if that field is text or doesn't contain the time. If it does contain time, then comparing 10/09/2017 to 10/09/17 11:51:47 PM will never be equal so a work around is needed. Not too difficult.

  7. #7
    tlyon is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    8
    Looks like it is Date/Time
    Click image for larger version. 

Name:	Date Time1.jpg 
Views:	15 
Size:	161.0 KB 
ID:	30713
    Attached Thumbnails Attached Thumbnails Date Time.jpg  

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Had you selected the date field in question, the properties would have been displayed for it. As it is, you're showing the ID field, so that doesn't answer all the questions I asked.

  9. #9
    tlyon is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    8
    OK, How is this

    Click image for larger version. 

Name:	Date Time1.jpg 
Views:	15 
Size:	161.0 KB 
ID:	30714

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes. So it is Short Date, which contains no time component and the default is Date(), so time should not be an issue based on the field you show.
    The criteria expression looks OK to me. However, I don't see how a date range solves your issue. You said you wanted a report for just one day?
    if i try and run a report for yesterday...I am using this criteria...
    Why not =SomeDate to get one day/date?

    If you get no results for a particular day and you know data exists for that day, and my question about a single date doesn't apply for some reason, then I'll presume you have joined tables in your query. I would start with a simple test before testing the joins.

    Copy the query, and in this copy change the date to an actual date from your table; e.g. #10/15/2017# and run it.
    If OK, clear that criteria value and try again using your forms! reference but pick the same date in one control only, and reference that control (this form is open when you're doing all of this, right?). If not OK now, something's happening to the single date being passed from the form to the query. If neither are OK, then test the joins.

    Assuming you have more than one table with equal joins between any/all of them (if there's only 1 table, joins are not the problem, but still do the next test)
    - put the hard coded date back in that you used (e.g. #10/15/2017#)
    - remove one field at a time from the query and run to test. If it eventually works, then either the last field you removed or that one and one or more others are causing a situation where the query cannot be satisfied as you think it should. That means the combination of fields and the criteria you're supplying results in no records that match.

    Another possibility is that you have outer joins on the query tables which along with the criteria, don't allow for expected matches.
    Last edited by Micron; 10-09-2017 at 10:46 PM. Reason: clarification

  11. #11
    tlyon is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    8
    Yes - No Joins
    Should I just change my criteria?
    what/How should the criteria be/look?

    Thanks

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The answer is in post 10
    Copy the query,...

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

Similar Threads

  1. Printing Daily Report for Existing Members
    By Rabihk in forum Access
    Replies: 3
    Last Post: 08-12-2015, 07:54 AM
  2. Replies: 2
    Last Post: 10-31-2014, 06:51 AM
  3. Auto email a pdf report daily from ACCESS
    By wsm_al in forum Access
    Replies: 7
    Last Post: 10-20-2011, 09:35 AM
  4. Replies: 9
    Last Post: 10-13-2011, 01:30 PM
  5. Daily Sales Report
    By bayswatergirl in forum Reports
    Replies: 1
    Last Post: 06-02-2011, 12:27 PM

Tags for this Thread

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