Page 1 of 4 1234 LastLast
Results 1 to 15 of 55
  1. #1
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82

    Invalid use of Null when trying to calculate a date based on a date in a previous record on a table.

    I am currently working on a database to record preventative maintenance preformed on equipment and to generate a report that list the equipment that needs to be serviced each month. I have two tables called Maintenance_TBL and Maintenance_Schedule_TBL and a sub-form on my main equipment tracking form called Maintenance_FRM_SUB. On the Maintenance_TBL I have two fields, Next_Maintenance_Date and Last_Maintenance_Date. I am trying to calculate the Next_Maintenance_Date base on the date in the Last_Maintenance_Date field of the previous record on the Maintenance_TBL.



    This is my current code:

    Code:
    Private Sub Next_Maintenace_Date_Click()Dim Maintenance_Interval As String
    Dim Number_Maintenance_Interval As Integer
    Dim DCalc As Date
    
    
    DCalc = DLast("Last_Maintenance_Date", "Maintenance_TBL")
    
    
    If Me.Parent!Maintenance_Schedule = 1 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 1
        ElseIf Me.Parent.Maintenance_Schedule = 2 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 2
        ElseIf Me.Parent!Maintenance_Schedule = 3 Then
            Maintenance_Interval = "q"
            Number_Maintenance_Interval = 1
        Else: Me.Parent!Maintenance_Schedule = 4
            Maintenance_Interval = "yyyy"
            Number_Maintenance_Interval = 1
        End If
    
    
    
    
       Me.Next_Maintenace_Date = DateAdd(Maintenance_Interval, Number_Maintenance_Interval, DCalc)
       
       
    
    
    End Sub
    I keep getting an Invalid use of Null error when I click on the Next_Maintenance_Date field on the Maintenance_FRM_SUB.

    I had this working yesterday afternoon and then I broke it. Since I am still in the building and testing phase I had made a few fake maintenance record for a piece of equipment. Then added the code to calculate the Next_Maintenance_Date based on what the user entered on the Last_Maintenance_Date of the previous record. At that point everything worked fine, then I tried to test something else and deleted all but the first of the fake maintenance records. I am thinking the reason that I am getting the error now is because there in no Last_Maintenance_Date in a previous record because I deleted the fake maintenance records.

    I have attached the database for reference.
    At this point, I am having trouble seeing the forest through the tress and would appreciate any help.

    Thank You
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Dlast does not mean latest, try using dmax instead

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    If there is no available date, perhaps use current date with the NZ() function?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You shouldn't be storing the next maintenance date, so the whole issue might be pointless. Your form should show the next maintenance date based on the date last performed plus the frequency.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    See if the attached now does what you need.
    I changed your Subforms to be Continuous Forms vice Datasheets.
    Now when you enter the Last_Maintenance_Date the Next_Maintenance_Date populates with the Date depending on the Schedule Period.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Micron View Post
    You shouldn't be storing the next maintenance date, so the whole issue might be pointless. Your form should show the next maintenance date based on the date last performed plus the frequency.
    Forgive my ignorance, I am still very much a newbie at Access. If I am not storing the next maintenance date how would I show the next maintenance date on the form? And I am trying to calculate the next maintenance date based on the last maintenance date and the frequency of the maintenance that needs to be preformed.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Last date of execution of the maintenance for a particular asset is stored and so is its PM frequency. Next date is shown in a form control as LastPMdate + Frequency using DateAdd function. So if last date was 12/01/2022 and frequency is 30 days, then control controlsource property is =LastPMdate + DateAdd("d",30,LastPMdate).
    This sort of stuff was a big part of my career in terms of preventive maintenance, condition monitoring and just plain maintenance.

    If the next date is stored and the last date was a typo that gets corrected, you have a problem - especially if you didn't plan for it. That's why it's generally accepted that storing calculations is a bad idea.
    Last edited by Micron; 12-28-2022 at 02:44 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by CJ_London View Post
    Dlast does not mean latest, try using dmax instead
    Using DMax does resolve the Invalid use of Null error message, but now I just get 01/27/2023 any time I click the Next maintenance date field. I just realized that I haven't entered any criteria when I am using DMax, so I am not grabbing the correct Last_ Maintenance_Date field from the Maintenance_TBL.

    I tried to set the criterial for the DMax function to try find the record when the Equipment_ID field of the Maintenance_TBL is equal to the Equipment_ID field of the Equipment_TBL, but I know that code is wrong because I am still getting 1/27/2023 for all of my next maintenance dates. How do I set the criteria for the DMax function so that I am grabbing the right information only when the Equipment_ID field of the Maintenance_TBL is equal to the Equipment_ID on the Equipment_TBL?

    Here is a copy of my revised code:

    Code:
    Private Sub Next_Maintenace_Date_Click()
    
    Dim Maintenance_Interval As String
    Dim Number_Maintenance_Interval As Integer
    Dim DCalc As Date
    
    
    DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", Me.Equipment_ID = Me.Parent!Equipment_ID)
    
    
    If Me.Parent!Maintenance_Schedule = 1 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 1
        ElseIf Me.Parent.Maintenance_Schedule = 2 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 2
        ElseIf Me.Parent!Maintenance_Schedule = 3 Then
            Maintenance_Interval = "q"
            Number_Maintenance_Interval = 1
        Else: Me.Parent!Maintenance_Schedule = 4
            Maintenance_Interval = "yyyy"
            Number_Maintenance_Interval = 1
        End If
    
    
    
    
       Me.Next_Maintenace_Date = DateAdd(Maintenance_Interval, Number_Maintenance_Interval, DCalc)
       
       
    
    
    End Sub

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My guess is that you've confused yourself by binding a control to equipmentid and naming it maintenanceid. Your subform has no control by the name equipmentid.
    Last edited by Micron; 12-28-2022 at 03:53 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.

  10. #10
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Okay, I think I figure out the issue with criteria part of the DMax function. I revised my code to:

    Code:
    Private Sub Next_Maintenace_Date_Click()Dim Maintenance_Interval As String
    Dim Number_Maintenance_Interval As Integer
    Dim DCalc As Date
    
    
    
    
    DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", "[Equipment_ID] =" & Forms![Equpment_Tracking_Form]!Equipment_ID)
    
    
    If Me.Parent!Maintenance_Schedule = 1 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 1
        ElseIf Me.Parent.Maintenance_Schedule = 2 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 2
        ElseIf Me.Parent!Maintenance_Schedule = 3 Then
            Maintenance_Interval = "q"
            Number_Maintenance_Interval = 1
        Else: Me.Parent!Maintenance_Schedule = 4
            Maintenance_Interval = "yyyy"
            Number_Maintenance_Interval = 1
        End If
    
    
    
    
       Me.Next_Maintenace_Date = DateAdd(Maintenance_Interval, Number_Maintenance_Interval, DCalc)
       
       
    
    
    End Sub
    and it appears to be working. I need to do some more testing to know for sure.
    Now for my own edification I would really like to know what is going on with the line of code

    Code:
    DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", "[Equipment_ID] =" & Forms![Equpment_Tracking_Form]!Equipment_ID)
    The first two parts of DMax make sense to me. I am telling access to get the maximum value in the field named Last_Maintenance_Date from the table Maintenance_TBL, but I get stumped on the third part in this case.


    "[Equipment_ID] =" tells access to look at the Equipment_ID Field on the current form, which in this case is the Maintenance_FRM_SUB

    then &Forms!{Equpment_Tracking_Form]!Equipemnt_ID is telling access to open the Equpment_Tracking_Form and get the record where the Equipment_ID field of the Equpment_Tracking_Form matches the Equipment_ID of the Maintenance_FRM_SUB?

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No, it is not opening the form, which by the way is already open? It's a reference to the value property (by default) contained in a control in a form that is your parent form. If you tried to use that syntax against the subform, it wouldn't work because subforms are not part of the forms collection when they are opened as a subform on a main form. You're getting away with that because the main form is a member of that collection. This also should work (at least not error)

    DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", Me.Maintenance_ID = Me.Parent!Equipment_ID)

    If your data type was string, then you'd have a problem due to lack of text delimiters, which you don't need for numbers.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    and it appears to be working.
    What happens to the existing dates in the prior records when you click in the Maintenance Due field for each of them?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Micron View Post
    Last date of execution of the maintenance for a particular asset is stored and so is its PM frequency. Next date is shown in a form control as LastPMdate + Frequency using DateAdd function. So if last date was 12/01/2022 and frequency is 30 days, then control controlsource property is =LastPMdate + DateAdd("d",30,LastPMdate).
    This sort of stuff was a big part of my career in terms of preventive maintenance, condition monitoring and just plain maintenance.

    If the next date is stored and the last date was a typo that gets corrected, you have a problem - especially if you didn't plan for it. That's why it's generally accepted that storing calculations is a bad idea.

    Okay, I see what you are saying now and that makes a lot of sense. If a mistake on one record is made the mistake will carry over to the following record because I am storing the calculated value in a table. But fixing the mistake in the first record won't fix the mistake in the other records and you wind up carrying that mistake over to the other subsequent records.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Once the mistake is fixed, the calculated date automagically adjusts. Once fixed, the mistakes don't recur? Yes, it would propagate onto the subsequent records, but worse, if you went back and corrected a typo, the fix does not propagate to the later records as you say. If you were not storing the next date, that would not be an issue would it? It's academic. You should have the date of execution and calculate from there. A factor not mentioned yet is PM type (if that's what this is about). Is it fixed or is it variable? Fixed means that when the annual was done a month late, it next comes due 11 months later. If variable, it's due 12 months after the last execution. Fun, no?

    and it appears to be working.
    What happens to the existing due dates in the prior records when you click in the Maintenance Due field for each of them?

    EDIT - forgot to mention, you still have the original issue if you click on a new record in the subform, yes?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Put the criteria into a string variable, then you can Debug.Print that until you get it correct, then you can use that in a domain function instead of the concatenated string.
    Plus if you have more than one criteria, that method comes in vey handy to see your errors.

    Even when you cannot, you can copy and post the output here or elsewhere for someone to advise where you have gone wrong.

    Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


    Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


    Numbers do not need anything


    Quote Originally Posted by drunyan0824 View Post
    Okay, I think I figure out the issue with criteria part of the DMax function. I revised my code to:

    Code:
    Private Sub Next_Maintenace_Date_Click()Dim Maintenance_Interval As String
    Dim Number_Maintenance_Interval As Integer
    Dim DCalc As Date
    
    
    
    
    DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", "[Equipment_ID] =" & Forms![Equpment_Tracking_Form]!Equipment_ID)
    
    
    If Me.Parent!Maintenance_Schedule = 1 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 1
        ElseIf Me.Parent.Maintenance_Schedule = 2 Then
            Maintenance_Interval = "m"
            Number_Maintenance_Interval = 2
        ElseIf Me.Parent!Maintenance_Schedule = 3 Then
            Maintenance_Interval = "q"
            Number_Maintenance_Interval = 1
        Else: Me.Parent!Maintenance_Schedule = 4
            Maintenance_Interval = "yyyy"
            Number_Maintenance_Interval = 1
        End If
    
    
    
    
       Me.Next_Maintenace_Date = DateAdd(Maintenance_Interval, Number_Maintenance_Interval, DCalc)
       
       
    
    
    End Sub
    and it appears to be working. I need to do some more testing to know for sure.
    Now for my own edification I would really like to know what is going on with the line of code

    Code:
    DCalc = DMax("Last_Maintenance_Date", "Maintenance_TBL", "[Equipment_ID] =" & Forms![Equpment_Tracking_Form]!Equipment_ID)
    The first two parts of DMax make sense to me. I am telling access to get the maximum value in the field named Last_Maintenance_Date from the table Maintenance_TBL, but I get stumped on the third part in this case.


    "[Equipment_ID] =" tells access to look at the Equipment_ID Field on the current form, which in this case is the Maintenance_FRM_SUB

    then &Forms!{Equpment_Tracking_Form]!Equipemnt_ID is telling access to open the Equpment_Tracking_Form and get the record where the Equipment_ID field of the Equpment_Tracking_Form matches the Equipment_ID of the Maintenance_FRM_SUB?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Calculate Date based on Past Date and Frequency
    By jchandler88 in forum Queries
    Replies: 9
    Last Post: 10-01-2018, 07:15 PM
  2. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  3. Replies: 1
    Last Post: 11-26-2013, 09:25 AM
  4. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  5. Replies: 34
    Last Post: 12-01-2011, 08:18 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