Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11

    Time Card Report

    Hi,
    I have a Time Card Access database which was built by https://access-templates.com/tag/time+card.html. The free database has several reports - one of which is 'Billable hours by employee'. This report shows employees, date worked, hours worked etc for the current week.
    I'm wanting a report which will display the same content but for the previous week but have no idea on how to accomplish this! I've tried copying the 'Billable hours by employee' report and looked at changing the date worked control source to Last Week using different combinations but I really don't have a clue what I'm doing.



    I did try emailing access-templates.com but did not receive a reply. Also, to register on their site costs $190. We are a tiny charity in the UK working with homeless people and cannot afford to shell out money on this.

    If anyone can help, please point me in the right direction

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I wasn't able to download the DB from the URL you specified. I used the original DB from Microsoft. Maybe this will help. Modify the query as below and it will know that the previous week starts on Sunday.

    Click image for larger version. 

Name:	Billable.png 
Views:	55 
Size:	51.4 KB 
ID:	43996

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    @ graisbeck , can you please post the record source of the existing report? You should be able to modify the criteria by using the DateAdd() to get the previous week.
    DateAdd("ww",-1,Date()) returns the date a week ago. So you need to plug in the function whatever date fields you have that define the beginning and end of the current week.

    https://www.w3schools.com/sql/func_msaccess_dateadd.asp

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    Quote Originally Posted by Gicu View Post
    @ graisbeck , can you please post the record source of the existing report?
    Hi Vlad, I'm not sure if this is what you're asking for!

    Cheers,
    Gary
    Attached Thumbnails Attached Thumbnails screenshot_access.jpg  

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not really Gary, you need to open the report in design view and look for Record source in the properties box. It could be a saved query or a SQL statement, clcik the builder (three dots) on the right to open it and copy the SQL statement in here.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    When I click the 3 dots the report opens in query builder as attached.Click image for larger version. 

Name:	report.jpg 
Views:	39 
Size:	213.5 KB 
ID:	44015

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is the report you show the original one that shows the worked hours for the current week?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    Quote Originally Posted by Gicu View Post
    Is the report you show the original one that shows the worked hours for the current week?
    Yes, that's correct Vlad.

  9. #9
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    I did try to upload the database to this thread, but it's 3mb so not allowed.

    Cheers,
    Gary

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Run a Compact and Repair first then zip it and upload the zip file.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    Vlad, please see attached.

    Cheers,
    Gary
    Attached Files Attached Files

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Gary, thanks for the file.

    I would say this is not a well designed database, it uses lookup fields in tables which most Access developers avoid as they create many issues in the long run see this for more info: http://access.mvps.org/access/lookupfields.htm).

    It looks like the db uses filters to filter the work days on the form, but the report in question (Billable Hours By Employee) is not filtered, as you can see it returns work dates in two different weeks.

    I have modified the form to convert macros to VBA and enabled the filters for the three hourly reports (billable by employee and project and non-billable by employee) and I created a new filter (they are stored in a table named Filters) for last week.

    In general not impressed by the quality of this template, I bet there are others out there much more robust but if it fits your needs...

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    Hi Vlad,

    Thanks for your help with this, it's much appreciated!

    I've now updated your updated version to include all employees etc and it's working great. It's on a mapped drive on my works PC and shared to 8 other users. I've had to limit the amount of users who can access the db to 2 because of users trying to save their hours on the same auto ID. Is there a way around this to allow access to more users?

    Thanks again!

    Regards,
    Gary

  14. #14
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Gary,

    Glad to hear it works!

    First of all if you are using in a multi-user environment you need to split it: https://support.microsoft.com/en-us/...3-51b1d73498cc

    Access has a built-in splitter that does the work for you, but you might need to review it once is done to make sure the table location makes sense (front-end vs back-end). Then you place the back-end on a mapped drive and give each user their own front-end. That should solve most of your problems with concurent users. To deploy updated versions of the front-end you can use some method of front-end management, I can set you up with my (free) version:http://forestbyte.com/ms-access-util...a-db-launcher/, I would need a charity logo if you want that on the launcher.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    graisbeck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Location
    United Kingdom
    Posts
    11
    Hi Vlad,

    That sounds great. I did try to split the db once before but got an error informing me that something was open so it could not be locked. Since then, I have realised it was because the work hours form was open! duh!

    I'm not sure that there will be anymore updates to the front end as I still do not understand how you created the new report . But your launcher does sound interesting so I've attached our logo to give it a try (not sure what resolution you require).

    Thanks again for your time.

    Regards,
    Gary
    Attached Thumbnails Attached Thumbnails DASHLogo-02.jpg  

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 07-18-2019, 01:36 AM
  2. QUERY: Date/Time with a wild card as criteria?
    By excellenthelp in forum Access
    Replies: 3
    Last Post: 04-21-2015, 03:06 PM
  3. Time Card Query Issue (dateDiff)
    By ShostyFan in forum Queries
    Replies: 3
    Last Post: 01-05-2015, 06:20 PM
  4. Programming a Report Card Application
    By mrovet in forum Programming
    Replies: 1
    Last Post: 06-16-2012, 02:11 AM
  5. Student report card design software
    By mado in forum Access
    Replies: 1
    Last Post: 11-27-2011, 04: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