calculate the next date with a condition based on another field in the table

[Apu91]

I have a field "x" with data : "3 and 4" for respective customers
I want the next date to be calculated for 1 year if the customer with x field is 4 and calculate 2 years if the field"x" is 3.

Right now to just calculate all the date with interbval of 1 year i have entered as
[LastTestDate]+364 but it calculates 1 year for all and does not satisfy the condition for customer with field x as 3

[jforbes]

A little more information will make it easer to answer this.

• How are you calculating this, is it in a Query or is it in code on a Form?
• Are you saving the calculated valve back into the Table?
• Can you paste your actual code here so we can see it?

[Apu91]

I am saving this in Calculated valve back into the table

[LastTestDate]+364

I dont know how to proceed with calculating the next date with condition mentioned in my first post. Is it a good idea to perform in form? if so how?

[jforbes]

Whether you perform this in a Form or in a Query will depend on your process. Either will work.

Something like this is usually taken care of at the time the Date is entered or changed. So if you or your users are entering the LastTestDate on a Form, then it could be taken care of by code on the Form or a Data Macro if you are using Access 2010 or newer (a Data Macro is probably the best method). If you are using a routine to bulk import data then a Query is often used to update the field in Bulk.

Now to really throw a wrench into the works, I need to point out that it's rarely a good idea to save data that can be easily calculated. It's a common practice to create a query for this, and in this case an advantage is that if your rules change on how many years to add, you can change just the query and everything continues to work. This is a related article that might shed some light on this concept: https://bytes.com/topic/access/insig...ble-structures

So, in a Query, you can do something like this:

SELECT *, IIf([SomeField]=4,DateAdd('yyyy',1,[LastTestDate]),DateAdd('yyyy',2,[LastTestDate])) AS SomeImportantDate FROM SomeTable

To return the original table as well as the calculated field. The same idea would be used to update a table.

In a Form, when saving the date in the table, the code is similar and looks something like this:

Private Sub LastTestDate_AfterUpdate()
    If Not Me!LastTestDate Is Null Then
        If Me!SomeField = 4 Then
            Me!SomeImportantDate = DateAdd("yyyy", 1, Me!LastTestDate)
        Else
            Me!SomeImportantDate = DateAdd("yyyy", 2, Me!LastTestDate)
        End If
    End If
End Sub