Access Date issue

[janelgirl]

I am new to access (using 2007), so please be gentle and speak in simple terms. I have two date fields in a query "InvoiceDate" and "PaymentDate". I would like to have it so that the PaymentDate has a default value based upon the InvoiceDate. However, if I need to change the PaymentDate on the rare occasion that it pops up, I still need to be able to alter PaymentDate whenenver I want. That being said.... here is the crazy thing that I would like to do. (both date fields are formatted as short date)

If the InvoiceDate is between 12/1/ANYYEAR and 12/31/ANYYEAR, I would like the PaymentDate to default to 1/15/CURRENTYEAR. Here's the rub. Depending on what the InvoiceDate is, the PaymentDate will change. So, I will need a code that can be adjusted as such. I would prefer no to put in years in the code simply because I am going to be using this program for some time (I hope), and would kind of need it to update itself for the current year. Hopefully this makes sense. Basically if I type "12/30" into InvoiceDate, I want PaymentDate to automatically put "1/15/11". Additionally, if I type "12/30/10" into the InvoiceDate, I want PaymentDate to automatically put "1/15/11". Also, if I type "12/30/11" into the InvoiceDate, I want Payment date to automatically put "1/15/11".



I've tried different wildcards and codes in VBA, but I have only confused myself more than I already am. TIA for any help that anyone can provide.

[ssanfu]

So are you looking for something like:

Invoice Date       Payment Date
----------------------------------------
12/20/2010         01/15/2011
12/30/2010         01/15/2011
12/30/2011         01/15/2012

02/25/2011         03/15/2011
03/28/2011         04/15/2011

[janelgirl]

That's it Exactly!

[janelgirl]

How do I do that?

[ssanfu]

You are using a form for data entry??

[ssanfu]

Using a form with two controls bound to two fields: InvoiceDate and PaymentDate, the after update event code for the field "InvoiceDate" would be:

Option Compare Database ' there should only be one of these
Option Explicit         ' there should only be one of these
'the two lines above should be at the top of every module

Private Sub InvoiceDate_AfterUpdate()

   Select Case Month(Me.InvoiceDate)
      Case 12   'December
         Me.PaymentDate = DateSerial(Year(Me.InvoiceDate) + 1, 1, 15)
      Case 1 To 11  'all other months
         Me.PaymentDate = DateSerial(Year(Me.InvoiceDate), Month(Me.InvoiceDate) + 1, 15)
      Case Else  ' not a month
         MsgBox "ERROR"
   End Select


End Sub

[janelgirl]

Worked like a CHARM!!! Thank you SO MUCH! That makes my life a lot easier!

[ssanfu]

You are welcome