Results 1 to 13 of 13
  1. #1
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7

    creating and updating a variable field

    Attached is a form with a "Lieu (hours)" column, that is summed in the "GT Lieu Hours" field.



    What is required is another field on the form: a "Lieu carried forward field" which is made from "a constant" ie Lieu Hours composed from the start of time, + current "GT Lieu Hours".

    Then when the form is generated in the next fortnight, the "Lieu carried forward field" will be made from "the previous constant" ie from the last report, + current "GT Lieu Hours".

    I am very new to access and have inherited this MS Access file, so please reply in simple terms.

    Thank you.
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    Trying to understand...

    In your report, you have
    3468...... 15-Jan-21 ............... (TOTAL TIL 44.25 + 14.5 = 58.75 HRS)

    The 14.5 hours is from the report for Dates Between: 4/Jan/2021 and 17/Jan/2021.
    1) Where does the 44.5 hours come from? Last year?
    2) Does the TIL hours ever get consumed? Does the TIL hours ever go down? If yes, how are the TIL hours reduced?



    And Welcome to the forum...
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7

    44.5 hours is a result from...

    I'm sorry I was not very clear. I have added 3 other attachments to help explain.

    (1) 44.5 hours is a result from when I started my employment. Sometimes in the fortnight I have negative hours, this keeps the hours from getting too big. These hours are my TIL (Time in Lieu)- as you figured out.
    (2) my aim is for the TIL hours to never get consumed. yes TIL hours do go down. this is done by adding negative hours (see all 4 attachments to help in clarification).

    Thank you for your "welcome to the forum", very much appreciated.
    Attached Files Attached Files

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    I think I would write a UDF to get the previous Lieu (hours) or total Lieu (hours). You could put the total in a control on a form/report.
    Not having seen your dB or tables/relationships, it is hard to be more specific.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7
    (1) can you give me an example of a UDF (User Defined Function) that will do what I want (and where I need to put this UDF). I understand I just can't plug it in, as you have not seen my DB with the specific names you need
    (2) How can I put the total in a control on a report, then use it in a calculation in the next report?

    If you need anything more from me that is specific (and will not breach confidentiality with the workplace), let me know.

    Thanks for your assistance, Steve.

    Cheers, Edward

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    Lets deal with (1) first. Do you have a table (tblEmployees) for employees (Edwards) and a table for hours (tblWorkHours)? If so, what are the field names?

    The UDF would be a Totals query filtered by EmployeeID and EndDate. Sum the "LieuHours" field.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7

    Tables & Field Names

    Hello Steve,

    The four attachments should have the answers requested.

    Cheers, Edward
    Attached Thumbnails Attached Thumbnails EdwardSigninQuery DESIGN.png   EdwardsSignIn Table Design.png   EdwardsSignIn Table Table.png  
    Attached Files Attached Files

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    OK, I revised the UDF example. I removed the employee table and added a function to calc current Lieu hours.
    Note the prefixes for object names. I removed any/all spaces in object names.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7
    Thank you very much Steve.

    You're a legend.

    It will take me a bit of time to comprehend what you have done.

    Also, as the report is generated every 14 days, is there a way of not entering the dates, for TIL but generating this +14 days from last set of 14 days?

    Again I thank you very much for your expertise and for being patient with me.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    You could enter the beginning date and have the ending date calculated.
    You could calculate the beginning date and the ending date based on today's date.
    You could have a 2 field table with all of the begin/end dates and do a look up using today's date the get the correct dates.


    I forgot to say that "DATE" is a reserved word and a built in function and shouldn't be used for object names.
    Also shouldn't use spaces in object names (field, table, query, form or report names).


    Good luck with your project......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7
    Thanks again, Steve.

    I'll keep you informed on my progress.

    Cheers, Edward

  12. #12
    ecConnect is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    7

    Thank you Steve

    I finally got it to work.

    I thank you Steve for helping me out.

    Cheers, Edward

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    You're welcome...
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Updating a variable Access Form text box from VBA.
    By jeraldkearney in forum Programming
    Replies: 10
    Last Post: 11-23-2017, 12:42 PM
  2. Creating a multi variable Search Form
    By AccessUser12345 in forum Forms
    Replies: 3
    Last Post: 10-14-2016, 01:36 PM
  3. Creating variable number of related records
    By msmithtlh in forum Forms
    Replies: 5
    Last Post: 06-13-2016, 04:28 PM
  4. Public variable not updating for each function
    By Ruegen in forum Programming
    Replies: 3
    Last Post: 08-28-2014, 06:26 PM
  5. Replies: 7
    Last Post: 03-02-2014, 08:47 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