469,903 Members | 1,759 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

Auto Calculate and Populate Date Field

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
Feb 25 '08 #1
6 11905
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?
Feb 25 '08 #2
JustJim
407 Expert 256MB
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
Feb 25 '08 #3
missinglinq
3,532 Expert 2GB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub CaseResolvedDate_AfterUpdate()
  2.   Me.Plus90TextBox = DateAdd("d", 90, Me.CaseResolvedDate)
  3. End Sub
  4.  
  5. Private Sub Form_Current()
  6.   Me.Plus90TextBox = DateAdd("d", 90, Me.CaseResolvedDate)
  7. End Sub
  8.  
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CaseResolvedDate_AfterUpdate()
  2.   Me.Plus90TextBox = DateAdd("m", 3, Me.CaseResolvedDate)
  3. End Sub
  4.  
  5. Private Sub Form_Current()
  6.   Me.Plus90TextBox = DateAdd("m", 3, Me.CaseResolvedDate)
  7. End Sub
  8.  
Linq ;0)>
Feb 25 '08 #4
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
Feb 25 '08 #5
NeoPa
32,231 Expert Mod 16PB
Another possible way to handle Null values is as follows :
Expand|Select|Wrap|Line Numbers
  1. =Iif(IsNull([CaseResolvedDate]),Null,DateAdd("d", 90, [CaseResolvedDate]))
I have to say I rather like Linq's solution though.
Feb 26 '08 #6
missinglinq
3,532 Expert 2GB
You know my signature, Ade:

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

;0)>
Feb 26 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Alistair | last post: by
1 post views Thread by Liz Malcolm | last post: by
7 posts views Thread by sheri | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.