By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

Trying to set a date field to autofill based on a another date field

P: 25
Hello,

I am trying to get a date field to autofill based on the date of another date field. I want the autofilled date to be 30 days after the other date. For example, if the user has entered 06/15 in the first date field, I want the second field to automatically populate with 7/15. I have tried everything I can think of to no avail! I can't imagine this is a difficult thing to do........

Anyone have any ideas?
Jan 14 '10 #1

✓ answered by NeoPa

The answer depends on how you intend to use this field to my mind. If you're entering new data then it's probably better to set the .DelaultValue property of the control. If it's simply for display, then set the .ControlSource to :
Expand|Select|Wrap|Line Numbers
  1. =IIf([DateField1] Is Null,Null,DateAdd('d',30,[DateField1]))

Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,638
Assuming your 2 Date Fields are [Date1] and [Date2], in the AfterUpdate() Event of [Date1], place the following code. When finished, it should look like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Date1_AfterUpdate()
  2.   'Is there a Value in [Date1] AND is it a Valid Date?
  3.   If Not IsNull(Me![Date1]) And IsDate(Me![Date1]) Then
  4.     Me![Date2] = DateAdd("d", 30, Me![Date1])
  5.   End If
  6. End Sub
Jan 14 '10 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
In the after update event of datefield1 put the following code ...

Expand|Select|Wrap|Line Numbers
  1. Me!datefield2 = DateAdd("d", 30, Me!datefield1)
Jan 14 '10 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
LOL! Snap
Jan 14 '10 #4

P: 25
Thanks ADezii!

I was trying something close to that, but apparently ooooh soooo far off!!! :)
Jan 14 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
The answer depends on how you intend to use this field to my mind. If you're entering new data then it's probably better to set the .DelaultValue property of the control. If it's simply for display, then set the .ControlSource to :
Expand|Select|Wrap|Line Numbers
  1. =IIf([DateField1] Is Null,Null,DateAdd('d',30,[DateField1]))
Jan 14 '10 #6

P: 25
@NeoPa
NeoPA, I see what you were talking about now. The date field is populating on the form, but it not in the table. How do get the date into the table as well?
May 11 '10 #7

missinglinq
Expert 2.5K+
P: 3,532
You don't! Very seldom is there a valid reason to store a calculated value in a table! It wastes storage space, and running a calculation is almost sure to take less time than retrieving a value form the disk.

Instead, you merely re-calculate the value as needed, in a form, report or query.

Welcome to Bytes!

Linq ;0)>
May 11 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
What he just said :)
May 11 '10 #9

P: 1
Not sure if this will help but I had a similar issue and this is what I did....

ACTION DESIRED: Have one date field trigger (calculate) future or past date values in other date fields in a form and (Most importantly)have those values save in the master database table and queries.

TRIGGER FIELD: Re Insp Date

AFFECTED CALCULATED FIELDS: Re Insp Date 90 Day (To be set to -90 from "Re Insp Date") Re Insp Date 60 Day (To be set to -60 from "Re Insp Date") Re Insp Date 30 Day (To be set to -30 from "Re Insp Date")

SOLUTION: Opened up the properties of what is to be the "trigger" date field went to 'All' tab Opened up a 'Code Builder' Event Procedure in the "After Update" property Entered the following VBA code

Expand|Select|Wrap|Line Numbers
  1.    Private Sub Re_Insp_Date_AfterUpdate()
  2.    Re_Insp_Date_90_Day = Re_Insp_Date - 90
  3.    Re_Insp_Date_60_Day = Re_Insp_Date - 60
  4.    Re_Insp_Date_30_Day = Re_Insp_Date - 30
  5.  
  6.    End Sub
NOTE: If there are spaces in the name fields, an underscore has to replace the spaces. As shown in the VBA code above, "Re Insp Date" field name must be coded as "Re_Insp_Date". (This is why many suggest you use no spacing in when naming database fields, tables, queries, forms, objects, etc)

Hopefully this helps someone else in the future.

Anyway, there it is.
May 20 '15 #10

NeoPa
Expert Mod 15k+
P: 31,494
David.

I applaud your intention to help, but am less sanguine about your posting this in spite of the strong advice that the intended solution is a very bad idea. Sometimes, to give someone what they ask for is not an act of kindness.

I'm not sure why you would do so after this has been clearly pointed out.
May 21 '15 #11

zmbd
Expert Mod 5K+
P: 5,397
There are a few times when storing calculated results can be justified - admittedly very few; however, for some things like discounts that may change over time or in my case where I do store certain correction factors due to legal/contractual obligations.

However, as missinglinq so clearly pointed out, this should not be the normative action.

As shown in the VBA code above, "Re Insp Date" field name must be coded as "Re_Insp_Date". (This is why many suggest you use no spacing in when naming database fields, tables, queries, forms, objects, etc)
Ok this is not an absolute requirement that the spaces be replaced... one need only enclose the names in brackets:
Expand|Select|Wrap|Line Numbers
  1. [Re Insp Date 90 Day] = [Re Insp Date] - 90
I do however strongly advise against using spaces in field names etc... as, IMHO, spaces needlessly complicate the coding and there are also other potential issues that can crop up :(
May 22 '15 #12

NeoPa
Expert Mod 15k+
P: 31,494
zmbd:
There are a few times when storing calculated results can be justified
Normalisation requires that values that can otherwise be calculated are not saved. If the ability to calculate a value is no longer there then saving that value contravenes no normalisation requirements. That would certainly be the case for time-relevant calculations such as you mention.
May 23 '15 #13

Post your reply

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