Auto Calculate and Populate Date Field

[awojciehowski]

I am working with a database now and need some guidance.

I am attempting to input an auto calculation. I have the ability to enter a date in a text box on a form. I want to be able to have another field that auto calcuates 90 days later and simiply displays that date. The auto calculated date does NOT have to be assiocated with a field in any table, just a caculation in what I believe would be referred to an unbounded box.

For example, I would enter 1/1/2008 and when I tab to the next field a calculation would occur below it in a box that would show a date 90 days later. (4/1/2008)

Any suggestions?

Thanks,
Adam

[awojciehowski]

I was able to figure out the correct coding:

=DateSerial(Year([CaseResolvedDate]),Month([CaseResolvedDate]),Day([CaseResolvedDate])+90)

However, I still need help...when there is no date entered into the CaseResolvedDate field "#error" is displayed. How can I hide that error message and only allow a date message to be displayed?

[JustJim]

I was able to figure out the correct coding:

=DateSerial(Year([CaseResolvedDate]),Month([CaseResolvedDate]),Day([CaseResolvedDate])+90)

However, I still need help...when there is no date entered into the CaseResolvedDate field "#error" is displayed. How can I hide that error message and only allow a date message to be displayed?

Hi,

Try looking at the help file for the DateAdd function, I think you will find it easier to use.

I don't know if that will stop your #ERROR problem though.

Jim

[missinglinq]

Several things. I assume from

=DateSerial(Year([CaseResolvedDate]),Month([CaseResolvedDate]),Day([CaseResolvedDate])+90)

that you're using this code in the Control Source for your unbound textbox. This means that when the record is created, Access tries to run the calculation, but at this point CaseResolvedDate is null, because it has no data in it. So you need to move this calculation to the AfterUpdate event of CaseResolvedDate. Since your second textbox is unbound, you'll also have to re-calculate each time you move back to a record. For the purpose of this example I've called the unbound textbox Plus90TextBox.

Next, Access has a function designed specifically for adding to dates, called DateAdd(), so we'll use that here.

Private Sub CaseResolvedDate_AfterUpdate()
  Me.Plus90TextBox = DateAdd("d", 90, Me.CaseResolvedDate)
End Sub
 
Private Sub Form_Current()
  Me.Plus90TextBox = DateAdd("d", 90, Me.CaseResolvedDate)
End Sub
 

The last thing is that adding 90 days to your original CaseResolvedDate of 1/1/2008 will not get you 4/1/2008, it'll yield 3/31/2008. So if you want 3/31/2008 the above code will work. If you actually want 1/1/2008 to yield 4/1/2008, you need to add 3 months instead of 90 days, and use this code instead.

Private Sub CaseResolvedDate_AfterUpdate()
  Me.Plus90TextBox = DateAdd("m", 3, Me.CaseResolvedDate)
End Sub
 
Private Sub Form_Current()
  Me.Plus90TextBox = DateAdd("m", 3, Me.CaseResolvedDate)
End Sub
 

Linq ;0)>

[awojciehowski]

missinglinq-

Thanks so much for your help! What a quick response. I copied your first set of VB code and it worked perfect. I was looking for 90 days, not 3 months so the 90 day date was perfect.

I really appreciate your help!

Adam

[NeoPa]

Another possible way to handle Null values is as follows :

=Iif(IsNull([CaseResolvedDate]),Null,DateAdd("d", 90, [CaseResolvedDate]))

I have to say I rather like Linq's solution though.

[missinglinq]

You know my signature, Ade:

"There's Always more than one way to skin a cat!"

;0)>