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

    Expression Data in one field returned into a following field in the same query.


    In the attached image a query can be seen. If we take the record "Depreciation" as an example. The first record has a fromdate021 of 01Jan2018. The Todate021a is a write able field and Todate021b is a calculated field. The expression currently in Todate021b is Todate021b: IIf([Todate021a] Is Null,Now()+60,[Todate021a]) Record POL0004 is the second record for Depreciation. The first record has a Todate021b of 31 Dec 2018, that date was captured. It means when the user registered record POL0004 he needed to go back to record POL0001 to capture the date. If a business changes its policy on the depreciation rate more times than twice, there will be multiple such records.

    I am trying to find an expression, maybe combining with the expression in Todate021b that will automatically set the Todate in record POL0001 when POL0004 is created. When POL0004 is created I want the user not needing to go back to capture 31Dec2018 manually. If there are multiple records for depreciation the expression will have to find the latest one, and if the fromdate021 in the new record is 01Jan2019, it mist display 31Dec2018 in Todate021b of record POL0001.
    Click image for larger version. 

Name:	BusPlan01.png 
Views:	12 
Size:	264.6 KB 
ID:	41474
    Last edited by Perfac; 04-05-2020 at 05:10 AM. Reason: Add something.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    why do you need a todate? The only time that would be relevant is when depreciation ceases, which it will never do. e.g. It might be relevant to when an employee leaves employment, but not when they change roles whilst employed.

    The risk you have with your current method is if you create gaps between the start data and the previous end date.

    Just populating the fromdate means the previous record end date is this records from date minus 1

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    In my system there are a few processes that will improve if I can achieve this. In South Africa the VAT rate changed 1 April 2018. Depreciation rates may change at 1 out of 1000 businesses, but it is good that the system can manage that. I have a salary review table where people salaries always change, an employee will earn a salary for a certain period and as far as I see I need a ToDate to calculate correct that from a Fromdate to a Todate the employee earned R***** and it changed, year after year. Our sales price like said in my last thread, you saw it, work the same. First my system calculates sales price automatically, but there is a table that works the same. The user can set a from and to date for a sales price, left open will mean that will be the price. Because in all cases the latest one has an unforeseeable ToDate the space get left opened in ToDate021a. Our process works fine.

    A mistake here can easily be made by a user not going back and entering the ToDate. That is what it is all about. It is always a sensation for me to solve these complicated ones, no matter who helps me. I love advice about things I miss or never knew.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    A mistake here can easily be made by a user not going back and entering the ToDate.
    suspect you have made it more complicated than it needs to be because you are allowing the users to be able to make mistakes and then having to write code to correct it rather than building an app that prevents a user from making a mistake in the first place. Agreed, they could still put the wrong from date - but they could do that whichever way. On your method, you have a number of potential updates to do - and if they put in the wrong year, you have a real mess to sort out.


    but suggest you need to use an update query to find the previous record and update the todate with fromdate-1 from the current record

    You don't appear to have a primary key so to identify the record you need you use something like

    dmax("FromDate021","myTable","FromDate021<#" & format([FromDate021],"mm/dd/yyyy") & "#" AND BusPoltern021='" & [BusPoltern021] & "'")

    so now you can find the record with the same description and dmax date and update it using an update query or recordset basis. You can use the dmax in a query but the query will be quite complex. Not enough information provided to suggest what it would look like would require linking the table to itself

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

Similar Threads

  1. Replies: 2
    Last Post: 11-07-2017, 06:07 PM
  2. Replies: 1
    Last Post: 06-01-2017, 09:29 AM
  3. Replies: 3
    Last Post: 07-01-2016, 08:11 AM
  4. Replies: 7
    Last Post: 11-25-2015, 07:06 PM
  5. Replies: 1
    Last Post: 07-24-2014, 08:35 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