Pre-Populate Date Based on Date in Field

[PinballWizard94]

Good Afternoon everyone,

I started using MS Access today so I apologise in advance for my lack of knowledge on the subject.

For years the company I work for have used MS Excel to log information about hundreds students. I have been looking into using MS Access as an alternate means of logging new students and editing current students info as and when it changes.

I have created a form where staff will input new students and their info, one of the fields is 'Start Date' and another field is 'Registration Date'. For our staff member who deals with registrations I would like the 'Registration Date' to automatically populate 42 days from the 'Start Date' when it is selected from a date picker.

I'm sure there must be a straight forward way to do this but so far I have been unsuccessful in finding a way on other threads and YT vids.

Thanks in advance!

 

[Gasman]

=StartDate + 42

 

[CJ_London]

dates are stored as decimal numbers - the value before the decimal point is the number of dates since 31/12/1899. The value after the decimal point is the time expressed in number of seconds so far/86400 (the number of seconds in a day) so now (14:02 on 24th November 2021) is

44524.5848148148

So simply add 42 to the start date for the registration date

in the registration control controlsource

=[startDate]+42

note if you are new to databases, google 'database normalisation' to understand how to construct your table. So for example on the basis of what you have told us so far, you should not be storing registration date since it can be calculated

 

[PinballWizard94]

=StartDate + 42

dates are stored as decimal numbers - the value before the decimal point is the number of dates since 31/12/1899. The value after the decimal point is the time expressed in number of seconds so far/86400 (the number of seconds in a day) so now (14:02 on 24th November 2021) is

44524.5848148148

So simply add 42 to the start date for the registration date

in the registration control controlsource

=[startDate]+42

note if you are new to databases, google 'database normalisation' to understand how to construct your table. So for example on the basis of what you have told us so far, you should not be storing registration date since it can be calculated

Thank you both for your getting back to me so quickly with your help, it's greatly appreciated!

I have managed to achieve what I wanted with [StartDate]+42 in a text box. Originally I was trying to input this formula into a date picker box and I think this is where I was going wrong.

I will also look into database normalisation @CJ_London. So I shouldn't store any information on my database that can be calculated (i.e. registration dates & end dates for courses)?

 

[Gasman]

I was thinking that the default would be 42 days but could be overridden?

 

[The_Doc_Man]

The only issue depends on what you wanted to do with the two dates AFTER they were defined. If both dates are being stored AND can be defined independently (but the registration date has a default of [StartDate] + 42), then you can't define the field's .ControlSource property using "=[StartDate]+42" - because that would mean you can't store the date. The ControlSource is a "one way or the other way" case and cannot go both ways. A field is either computed or bound, can't be defined as both. But if you stored the default for your bound registration date in another way such as using VBA, that would work. E.g. use a {StartDate] LostFocus event to add code such as

Private Sub StartDate_LostFocus()
    If NZ( [RegistrationDate], 0 ) = 0 Then
        [RegistrationDate] = [StartDate] + 42
    End If
End Sub

As to storing registration dates, end dates, etc. you should not store things that can be computed, but perhaps an illustration would help. Let's say you have a course that is 90 days long. So... you have three potential date-related values. Start Date, End Date, and Duration. BUT since they are related, storing any two of them allows you to compute the third value. Here, you would store two values. But suppose the end-date varies due to some other factor - like, in south Louisiana we have "hurricane days" on schools and a class might extend slightly. Or consider stoppage time in a soccer match, where a half is 45 minutes plus stoppage time. The point being that you should decide which elements are independent and which ones are dependent, 'cause you can always recompute the dependent elements.

 

[theDBguy]

Hi @PinballWizard94. Welcome to AWF!

 

[CJ_London]

just to add to Doc's comments

Say the start date is a Monday - and 6 weeks from then is a bank holiday - would the registration date on the bank holiday or moved forwards or backwards to a 'working' day. If that is the case your computation becomes a bit more complicated (but still doable)