Share via

In Access, VB, I am needing help with a IF/THEN scenario involving dates.

Anonymous
2025-05-27T15:00:28+00:00

I have a form that I use for task scheduling and keeping track of dates when I performed that task. It is a simple form where I have a "STATUS" field combo box with "Not Started" and "Completed" for source options. Also, in the sourced table, I have columns identified for each of the four quarters in the year. I need help with the code that, if "STATUS" is selected "Completed", and today is, for example, is in the second quarter of this year, THEN enter "TODAYS DATE" in the "Quarter2" field.

Thanks,

Jeff Bomberger

Microsoft 365 and Office | Access | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2025-05-27T16:20:05+00:00

    I think you need something like

    DIM Q AS BYTE
Q = DatePart("q", Date()) 

If Q = 1 Then CompletedDate1 = Date() 
If Q = 2 Then CompletedDate2 = Date() 
If Q = 3 Then CompletedDate3 = Date() 
If Q = 4 Then CompletedDate4 = Date()
    1 person found this answer helpful.
    0 comments

11 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-27T15:30:44+00:00

    I am not very happy with the 4 fields. Once you have a date, the quarter is derived data.

    Maybe you can just store the date in a field (DateCompleted). If you want to show the quarter, use the DatePart function to calculate the quarter. There is no need to store that.

    Look HERE

    0 comments
  2. Anonymous
    2025-05-27T15:52:04+00:00

    I need to store multiple dates in the table is the reason for the 4 fields. I have a task that is completed every quarter, and I need to know the exact date in that quarter that I completed that task. I tried to use a "IF NULL" or "BLANK" Then Else statement, but it did not work right...

    Thanks,

    Jeff

    0 comments
  3. Anonymous
    2025-05-27T15:59:14+00:00

    Then show the code........

    0 comments
  4. Anonymous
    2025-05-27T16:01:47+00:00

    Here is my code...

    Private Sub Status_AfterUpdate()

    On Error GoTo Status_AfterUpdate_Err 

    If (Not CurrentProject.IsTrusted) Then 

    Exit Sub 

End If 

If ([Status] = "Completed") Then 

    [CompletedDate2] = Date 

End If 

If ([Status] = "Completed") Then 

    [DueDate] = DateAdd("ww", [Frequency], [DueDate]) 

End If 

If ([Status] = "Completed") Then 

    [StartDate] = DateAdd("ww", [Frequency], [StartDate]) 

End If 

If ([Status] = "Completed") Then 

    [Status] = "Not Started" 

    [In Service] = False 

End If 

DoCmd.Close acForm, "Task Details", acSaveYes

    Status_AfterUpdate_Exit: 

    Exit Sub

    Status_AfterUpdate_Err: 

    MsgBox Error$ 

Resume Status\_AfterUpdate\_Exit

    End Sub

    0 comments