Date Calculation

[dharsh]

I have a date field called [BDM Start Date]. I need to add 180 days to this date and then round it to the 1st of the next month. Can anyone help me with this? I am using Access 2010. Thanks

[Martinc4611]

You could do something like this:

Function NewDate()

Dim Startdate As Date ' your date field
Dim dd As Integer ' days

'initalize startdate value
Startdate = Now()

Startdate = Startdate + 180
dd = Format(Startdate, "d")

'Check for StartDate = first day of month, advance 1 day.
If dd = 1 Then Startdate = Startdate + 1

'Loop to get first day of next month
Do Until dd = 1
Startdate = Startdate + 1
dd = Format(Startdate, "d")
Loop

MsgBox Startdate
End Function

[dharsh]

I am a novice in Access. Is there an easier way to do this in a query?

[Martinc4611]

No easy way that i know of. Here is a way you can do it in a query.

1. Create a new module in Access. Copy the code below and save it.
2. In your query, paste the following in a new field: NewDate: CalcDate([Modified_Date])
3. Run your query. The field NewDate will contain the information you are looking for.

copy this to a new module.

Function CalcDate(OldDate As Date)

Dim dd As Integer ' days

OldDate = OldDate + 180
dd = Format(OldDate, "d")

'Check for StartDate = first day of month, advance 1 day.
If dd = 1 Then OldDate = OldDate + 1

'Loop to get first day of next month
Do Until dd = 1
OldDate = OldDate + 1
dd = Format(OldDate, "d")
Loop

CalcDate = OldDate
End Function