Issue with dates

[MichaelSB]

Hello, I have been trying to work a little with VBA and have run into one issue so far with dates.

Right now my form has a drop down combo box and in the combo box you select a worker and it gives that combo box a unique value associated to selected worker. Next I have a text box to enter a date you would like to assign to the worker you selected. Finally you click the button to add this date to two tables where the selected workers unique id matches. One table is just a running log of all dates ever assigned to a worker and the other table is just the most current date assigned. Below is my current code for the button to make this happen.

Option Compare Database


Private Sub Add_Augment_Click()
    If IsNull(Me.Input_Staff) Then
        MsgBox "Staff Member needs an entry!"
        Cancel = True
        Input_Staff.SetFocus
    ElseIf IsNull(Me.Input_Date) Then
        MsgBox "Date needs an entry!"
        Cancel = True
        Input_Date.SetFocus
    Else
        Dim Aug_Date As String
        Aug_Date = DLookup("Augment_Date", "Staff_List", "WORKERID = ([Input_Staff].Value)")
        If Me.Input_Date.value <= Aug_Date Then
            DoCmd.RunSQL "INSERT INTO Augmentation_Dates (WORKERID, Augment_Date) VALUES (([Input_Staff].Value), ([Input_Date].Value))"
            Me.Input_Staff.value = Null
            Me.Input_Date.value = Null
        Else
            DoCmd.RunSQL "UPDATE Staff_List SET Augment_Date = ([Input_Date].Value) WHERE WORKERID = ([Input_Staff].Value)"
            DoCmd.RunSQL "INSERT INTO Augmentation_Dates (WORKERID, Augment_Date) VALUES (([Input_Staff].Value), ([Input_Date].Value))"
            Me.Input_Staff.value = Null
            Me.Input_Date.value = Null
        End If
    End If
End Sub

The purpose of this setup currently is so the Staff_List date only ever displays the most current date entered for any given worker. So if JOHN has a most recent date(Augment_Date) of 8/27/2020 and you try to enter 8/26/2020 for him, it will still make a entry no matter what in the Aumentation_Dates table but it would not update his most current date in the Staff_List table as I want that to stay the most recent date assigned.



The issue I am running into is currently I have JOHN with is most recent Augment_Date of 10/1/2019. When I add a date from 2/1/2020 on it will update the date in the Staff_List as it should since it is a more current date, but if i enter a date in January of this year it does not update in the Staff_List as if 1/15/2020 is not more current than 10/1/2019. Could anyone assist me in figuring out as to why that would be happening? Also this is the first time I have tried messing with access so if someone could also maybe help me clean up my above code so it is proper I would appreciate it. Comparing the proper formatted code to mine would really help me learn a bit better. Thanks to all who help.

[June7]

This could happen if date fields are text type and not date/time type and/or if date variables are declared as String instead of Date as you show. Because then alpha sort rules apply and / character is lower than numbers so 1/ sorts before 10.

Need to concatenate variables for building SQL statement.

Private Sub Add_Augment_Click()
With Me
    If IsNull(.Input_Staff) Then
        MsgBox "Staff Member needs an entry!"
        Cancel = True
        .Input_Staff.SetFocus
    ElseIf IsNull(.Input_Date) Then
        MsgBox "Date needs an entry!"
        Cancel = True
        .Input_Date.SetFocus
    Else
        If .Input_Date <= Nz(DLookup("Augment_Date", "Staff_List", "WORKERID = " & .Input_Staff), 0) Then
            DoCmd.RunSQL "INSERT INTO Augmentation_Dates (WORKERID, Augment_Date) VALUES " & .Input_Staff & ",#" & .Input_Date & "#"
        Else
            DoCmd.RunSQL "UPDATE Staff_List SET Augment_Date = #" & .Input_Date & "# WHERE WORKERID = " & .Input_Staff
        End If
        .Input_Staff = Null
        .Input_Date = Null
    End If
End With
End Sub

Should be 1 table and use query to select most current record for each worker. Use DMax() to get most recent date value.

What purpose should Cancel = True serve? It really means nothing in this event, in fact, should cause error as undeclared variable if Option Explicit is in module header (which it should be).

[MichaelSB]

Perfect! I simply changed "As String" to "As Date" and it fixed my issue with the date input. Thanks a bunch. I also appreciate your suggestion for one table. I will work on that.

If anyone else has some input as well that would be great as well. Are there some changes I can make with the code itself as it is currently that would be "more correctly" formatted. Thanks again.

[June7]

You probably read my post before finished edits. Might review again.

[MichaelSB]

Question about using only 1 table. If I just use 1 table wouldn't it add a new entry every time I add a new augment for the same person? Thats what I was trying to avoid by using 2 tables. Reason being is because the staff_list table has other information in it as well. an example is in the picture provided.



So even though I only have 3 staff listed, it's showing 7 staff member entries since jane and bob have both been augmented 3 times. The only entry I want stored here is their most current date augmented. I still want to be able to track every augment date each staff member has had but I dont really want to see it here. I believe that is where your suggestion to use queries to filter the data comes in which I understand but I also use this table to check or uncheck the staff members augment status as need which in my case, changes often. If I allow for the same staff member to show up in this tables every time they are augmented, if they change augment status from yes to no, I may have to uncheck the button for them on 20 entries vs just 1. With this information does it now make more sense 2 have 2 tables or am I overlooking something still and maybe it still is better to have 1 table. Agian, this is a learning curve for me so everyone's help is very much appreciated.

[June7]

What determines augment status of yes or no? Likely it can be calculated.

Should have a Staff table that has info about each individual such as name, ID, phone, department, DOB, hire date, etc. - perhaps even augment status field.

But augmentations should all be in 1 table. Use query to retrieve most recent augmentation. What is an augmentation?

[MichaelSB]

Augmentation is when someone is hired for a specific job but is then utilized for another job temporarily. An example, a companies HR Manager primarily only works as the HR Manager but if staffing levels are not ideal in the receiving department because people are on vacation, at training, out sick, or even out sick due to COVID at this time, the HR Manager can be "Augmented" to work in receiving for the day. This cant really be calculated due to the fact that the criteria that says you can or can not be augment changes day to day. For instance normally HR staff could be augmented but if they have a audit going on or new staff to hire and train they may get an exemption from augmentation during that time. This same criteria effect many departments so it is just easier to have a checkbox that I can check and uncheck as needed for each individual.

So from the sounds of it after reading your reply, the best method moving forward would be to simply have the staff information in one table and all augmentation dates in another then utilize queries to manipulate the data and display it how I need it correct? Thanks for your time once again.