Results 1 to 7 of 7
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Refreshed date.

    What date expression returns a refreshed date? Can it be done in a table where the date changes to Now every time the table is opened. Or is it done at a query?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Need more context. You can have a routine to update a date of your description in your table when some event occurs.
    If you are asking about files in the filesystem generally, then I think you can get create and modified dates.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not sure why you want to do this, but I would suggest doing it at form level if there is a good reason for doing so.
    If the field is always going to be equal to now what is its purpose... Unless just to show the current date and time.
    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

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I thought there may be multiple needs for it and it could be a general request. One place where I want it, is in the salary table. Every month the TAX on salary may be influenced by age. One month a person may be in a different group than the next month. I accept that a form can do the thing but for the moment I thought of putting it in a table or query as a field in every record. The date must be refreshed all the time, or can it only happen when the object is opened?

  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
    I see no point in you saving the current date & time - let alone saving it as a field in every record.
    In fact it would go against all standard database principles.
    Doing so would create a calculated field that by definition would be immediately out of date
    It would also waste disk space unnecessarily
    When you need the value to update another field such as tax group, use Now() in an update query
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You cannot expect Access to modify a table's existing records via a calculated field simply because you open the table. Not sure what you mean by this since you shouldn't be opening a table anyway as a routine thing, which is what this sounds like. Besides, the calculation can't use fields from other tables or queries and the result is read only and is limited to latter Access versions, but maybe that doesn't matter here. Then there is the aspect where if you're only talking about a default value, this value doesn't exist in an unsaved record, which is what you're seeing at the bottom of a table where a new record is started but not saved. Maybe that's not relevant since you seem to want to use this calculation going forward, so it probably needs to be a saved value.

    Second, what would you be doing with the result of this calculation - basing taxation rates on a person's age right? What happens when you look at records from the last period (say, 10 years ago)? Are all those old taxation rates looking at the new calculated rate? I sure hope not. The rate should be calculated on a form by comparing birth date to Date() and either looking up the applicable rate from a table, or calculating it in code. Then what you store is the result of the calculation that is current. This would be one of the rare examples of where a calculation needs to be saved - due to historical reasons. Besides, it seems to me that to do this for more than one person would require a calculated field for each person - and we all should know that that would be an example of poor design.

    Maybe take a look here. Then if you must store the calculation, you might be better informed, but that assumes you aren't aware of what's published there.
    Last edited by Micron; 04-08-2018 at 04:38 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I can't claim to be a total rookie anymore, so I am a bit embarrassed. =Date() solves what I want to do. In a table =Date() does not update whenever the table is opened. A calculated field in my query q01Employee does. There is a query q03MonthlySalarySummary. In South Africa there are different tax tables when an employee turns 65 and 75. Even though it is at a high age my development should do the correct calculation of course. That query have to automatically calculate the age so that the correct PAYE is calculated. Not something I want the user to "remember". My process of choice is to have this query q03MonthlySalarySummary being able to calculate any month I request a summary whether the user's Business have 5 or 100 staff. Also when I request a summary from this query 30 months in the past, the calculations should calculate correct. Maybe the age of an employee is 67 now, and at the time I want the request 64. I have actually already achieved that. Our process of choice is for every current month that query delivers a summary of salaries with deductions and when approved an append query "writes" the data to table t03Salaries. Any mistakes can be fixed through a form called f02SalaryInput. No calculations in t03Salary but many in q03MonthlySalary. So Micron I feel there is use for what I asked for. Thank you.

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

Similar Threads

  1. Report saved in pdf is not refreshed
    By myaccesss in forum Reports
    Replies: 2
    Last Post: 02-25-2016, 06:04 AM
  2. Replies: 12
    Last Post: 10-30-2014, 01:59 PM
  3. LInked tabled refreshed growing mdb file
    By PATRICK in forum Access
    Replies: 1
    Last Post: 03-26-2013, 12:16 PM
  4. Replies: 3
    Last Post: 09-14-2012, 04:10 PM
  5. Refreshing subform when form refreshed
    By Remster in forum Forms
    Replies: 27
    Last Post: 10-15-2010, 09:39 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