Calculating Service Period from Statement Date

[tkbeard29]

Hello All,



I'm use a form as a data entry that flows to a table. In the data entry form I have a field titled "StatementDate", that field is used to calculate the "DueDate". I'm trying to use the StatementDate field to calculate a ServicePeriod field as well with the following criteria:

If the statement day is the 15th or later than the service period would be the next month, if before the 15th the service period is the month of the StatementDate. Example

StatementDate = 1/14/2013, then the ServicePeriod would be 01/2013
StatementDate = 1/15/2013, then the ServicePeriod would be 02/2013


The VBA Code to calculate the DueDate is as follows:

Private Sub DueDate_Enter()
DueDate = DateAdd("d", 14, Me.StatementDate)
End Sub

I'm not sure, but I'm guessing I'll have to use the DatePart function to pull the day from the StatementDate?

Any input or tips on trying to achieve the desired results?

[RuralGuy]

Have you looked at the Day() function yet?

[tkbeard29]

I hadn't, but that would be easier than the DatePart function. I will admit, I'm not the best at VBA coding when it comes to If/Then/Elseif statements.

So from my example above of StatementDate = 1/14/2013, I'd want the ServicePeriod to be 1/1/13 and then the format of the field in the table converts it to 01/2013, and if StatementDate = 1/15/2013 I'd want the ServicePeriod to be 2/1/2013.

Having the consistent Service period of the 1st day of the month makes it easier to filer, at least how I have it set up right now. Is there an easier way to do that, or is that the best way?

[tkbeard29]

Finally got a chance to get back to building my database today, I'm not sure if the following code would work... I'm going to say no, but maybe this blind squirrel found a nut?

Private Sub ServicePeriod_Enter()


ServicePeriod = If Day([StatementDate]) >= 15 Then [ServicePeriod] = "(Month([StatementDate]+1),1,(year([StatementDate]))
Elseif Day([StatementDate]) < 15 [ServicePeriod] = "(Month([StatementDate]),1,(year([StatementDate]))
End If

[RuralGuy]

You've got the right idea but you don't need the test If < 15. What else could it be? You will also work out your construct when you compile it.

[tkbeard29]

I'm getting a lot closer...

Private Sub ServicePeriod_Click()
 If Day([StatementDate]) >= 15 Then
[ServicePeriod] = "(Month([StatementDate]+1),1,(year([StatementDate]))"
Else: [ServicePeriod] = "(Month([StatementDate]),1,(year([StatementDate]))"
End If
End Sub

But now I'm getting an error:
"Run-time error '-2147352567 (80020009)'"
The Value you entered isn't valid for this field.

So do I need something about Date Format in the code?

I tried putting # in front of and behind the result sequence, but that returned the same error.

[RuralGuy]

Try:

Private Sub ServicePeriod_Click()
    If Day([StatementDate]) > 14 Then
       [ServicePeriod] = DateSerial(Year([StatementDate),Month(StatementDate)+1),1)
   Else
       [ServicePeriod] = DateSerial(Year([StatementDate),Month(StatementDate),1)
   End If
End Sub

[tkbeard29]

Fan-freakin-tastic!!!

Thank you very much for the help!

[RuralGuy]

You're very welcome. Keep in mind the code does not work in December.

[tkbeard29]

Looks like I may have spoken too soon, it isn't doing the month +1 if the StatementDate is over 14.

Private Sub StatementDate_AfterUpdate()
    If Day([StatementDate]) > 14 Then
    [ServicePeriod] = DateSerial(Year([StatementDate]), Month([StatementDate]), 1)
   Else
    [ServicePeriod] = DateSerial(Year([StatementDate]), (Month([StatementDate]) + 1), 1)
   End If

[RuralGuy]

Can you single step the code and see what the Day() is returning?

[tkbeard29]

I put in 1/14/13 as the StatementDate, it's returning 01/1900 as the result. If I click on the cell ServicePeriod in the form it shows 1/13/1900. That's using this code:

Private Sub StatementDate_AfterUpdate()
  ServicePeriod = Day([StatementDate])

[RuralGuy]

Is your [StatementDate] a field in a table or a calculated field in a query?

[tkbeard29]

It's a field in a table, as is ServicePeriod.

StatementDate - Date/Time (format mm/dd/yy)
ServicePeriod - Date/Time (format mm/yyyy)

[RuralGuy]

Are you displaying the [StatementDate] and is the control also named [StatementDate]? If so then change the Control name to [txtStatementDate] and see if the results change. Do you know how to single step code?