Results 1 to 15 of 15
  1. #1
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8

    Report Help

    So i am pretty new to Access. I have a very simple access database with 2 forms and one report. All i want is when i click the button on my form to generate a report, I just want it to include the data for the current date. I have spent hours on trying to get this to work and I have no idea how to do it.



    Eric

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    build a query to pull the data you need.
    then use the report wizard to use that query and make the report.

    if the query needs 1 date, under the criteria for the date field, put a message in brackets:
    [Enter Date]

    then the query will pull only records for that 1 day the user enters.

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Build a query using the table that is bound to the form. Set the criteria in the query for the data required. Use the query as the record source for your report.

  4. #4
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    I will try this. I just wish there was something that says use current date for report?

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As already stated, create a query with the fields you need for the report.
    Filter your date field by typing Date() in the Criteria row and save it. This will just display the results for the current date
    Now use that query for your report record source
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by isladogs View Post
    As already stated, create a query with the fields you need for the report.
    Filter your date field by typing Date() in the Criteria row and save it. This will just display the results for the current date
    Now use that query for your report record source
    Worked Great. Now another question. I need in the criteria field of my time to just include the last hour. This syntax does not appear to be working

    "Time"<=Now(1/24)

  7. #7
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    That worked. Now I am trying to put in criteria for time because i only want the query to show last hour. I have tried everything. What do i put in the criteria for the time field?

  8. #8
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by ranman256 View Post
    build a query to pull the data you need.
    then use the report wizard to use that query and make the report.

    if the query needs 1 date, under the criteria for the date field, put a message in brackets:
    [Enter Date]

    then the query will pull only records for that 1 day the user enters.
    That worked. Now I am trying to put in criteria for time because i only want the query to show last hour. I have tried everything. What do i put in the criteria for the time field?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What do the values in your date field look like? Do they show time values? If not, check the table field in design view and see if you have applied a format. If so, remove it (or do this on a copy of your table if you are unwary of modifying it) and check again. If you don't have the time portion in your field, you cannot filter on time.

    Or try >= DateAdd("h",-1,now())
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Micron View Post
    What do the values in your date field look like? Do they show time values? If not, check the table field in design view and see if you have applied a format. If so, remove it (or do this on a copy of your table if you are unwary of modifying it) and check again. If you don't have the time portion in your field, you cannot filter on time.

    Or try >= DateAdd("h",-1,now())
    I had a date field AND time field seperate. I have recently DELETED both fields and recreated a field called Date AND Time. The field for Date and Time does NOT show a time value.

    I assume that Date/Time field must include both?

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Deleting them was probably a mistake. When only dates entered into a date/time field the value looks like 01/01/2020 and the time portion defaults to 00:00:00 A.M. (at least I think it's AM). Thus you can't retrieve records based on time as all records with 01/01/2020 are timed as 00:00:00.
    You should go back to the previous version of your table (you are making such adjustments on db copies, right?) and try

    >= DateAdd("h",-1,Time())

    in your time field, and = Date() in your Date field. That should return records for the current date where the time is anything in the last hour.
    IMHO there is no reason to store date and time in separate fields.
    If you need to show 01/01/2020 in one form field and 01:30:00 AM in another, you just split 01/01/2020 01:30:00 AM. As for how it looks in a table, users should never be interacting with tables anyway, so how it's laid out doesn't matter.
    Last edited by Micron; 02-19-2020 at 01:22 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Micron View Post
    Deleting them was probably a mistake. When only dates entered into a date/time field the value looks like 01/01/2020 and the time portion defaults to 00:00:00 A.M. (at least I think it's AM). Thus you can't retrieve records based on time as all records with 01/01/2020 are timed as 00:00:00.
    You should go back to the previous version of your table (you are making such adjustments on db copies, right?) and try

    >= DateAdd("h",-1,Time())

    in your time field, and = Date() in your Date field. That should return records for the current date where the time is anything in the last hour.
    IMHO there is no reason to store date and time in separate fields.
    If you need to show 01/01/2020 in one form field and 01:30:00 AM in another, you just split 01/01/2020 01:30:00 AM. As for how it looks in a table, users should never be interacting with tables anyway, so how it's laid out doesn't matter.
    I am perfectly fine with it both included in one field. I just need to know now on the Form side how do i get it to a point, where i click the Date calendar it also enters the time, to show date and time inside the form field. Screenshot below but I want them to select date and it populates the date AND time in the field. I need to query off of date and then time.
    Click image for larger version. 

Name:	formtime.JPG 
Views:	16 
Size:	44.5 KB 
ID:	40964

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Since the built in date picker is sorely lacking (to put it mildly) developers will often use a custom calendar control or form with a time based control as well. If you need a date picker with time then you might be forced into that solution. It is about the only way I know of to pick a date prior to today and still be able to store a time that has already passed. You can concatenate today's date and 4:09:53 PM in the control's AfterUpdate event but I doubt there is a point to picking yesterday's date and being stuck with the current time to go along with it.

    Is there some reason why you can't forego the date picker and save a record with Now() and lock down that form field?
    There is another question - you had a time field before. How did those values get entered? Might be simpler to go back to what you had and in your query date field put Date() as was suggested, and put >= DateAdd("h",-1,now()) as criteria in the Time field as suggested. As long as you're using Date() and not allowing users to enter a date you should be OK getting records for today going one hour back.

  14. #14
    Flucky07 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Micron View Post
    Since the built in date picker is sorely lacking (to put it mildly) developers will often use a custom calendar control or form with a time based control as well. If you need a date picker with time then you might be forced into that solution. It is about the only way I know of to pick a date prior to today and still be able to store a time that has already passed. You can concatenate today's date and 4:09:53 PM in the control's AfterUpdate event but I doubt there is a point to picking yesterday's date and being stuck with the current time to go along with it.

    Is there some reason why you can't forego the date picker and save a record with Now() and lock down that form field?
    There is another question - you had a time field before. How did those values get entered? Might be simpler to go back to what you had and in your query date field put Date() as was suggested, and put >= DateAdd("h",-1,now()) as criteria in the Time field as suggested. As long as you're using Date() and not allowing users to enter a date you should be OK getting records for today going one hour back.
    That worked thank you. One last thing pretty please. I have a report opening in report view. This will be opened, maximized to full screen and i want it to update the data that is put in from another form every minute. How the heck do i do this? I created a on timer 'event procedure'
    Private Sub Report_Timer()
    Me.RecordSource = Me.RecordSource
    End Sub
    Timer interval 30000

    BUT it only is updating under my report when having the back end database opened as well. It is not update my other users report automatically.
    Thoughts?

    Thanks

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't believe I've ever needed to do this but I'd try Me.Requery. However, I don't understand what this means
    BUT it only is updating under my report when having the back end database opened as well. It is not update my other users report automatically.
    Whoever opened the report will get any updates - not other users. The report is in their front end? Usually a report is for providing a snapshot view of data. If you want to continually update wouldn't a form be better suited?

    30000 is 30 seconds, not a minute. Whatever you end up using, you might find that there's a performance hit if going across a network. Hope you're not doing this over wifi.

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

Similar Threads

  1. Replies: 16
    Last Post: 03-13-2018, 05:24 PM
  2. Replies: 9
    Last Post: 07-03-2017, 11:24 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