Automatically filling a text box based on another

[miss_sunshine]

I have 2 fields called "start date" and "expiry date". The expiry date is always 6 months, less a day, than what the start date is. Rather than the user calculate the expiry date in their head when filling out the form, I'd like it to appear automatically as soon as the start date is entered. If not, at least when the record is saved.

Can this be done?

 

[sonny]

Not tested but may work.

=DateAdd("m", +6, "d" -1[StartDate])

 

[sonny]

That didnt work....
I tried this in a query and it works doing the the math to add the 6 mos first then subtracting a day from that. Gotta be a single way to do this though...

SELECT DateAdd("m",+6,[StartDate]) AS Test, tblTeamMembers.StartDate, tblTeamMembers.TeamID, DateAdd("d",-1,[Test]) AS Test2
FROM tblTeamMembers;

This line worked Test: DateAdd("m",+6,[StartDate])-1

I knew there had to be a way!

 

[Ian Mac]

you could also use some code:

Private Sub Date_BeforeUpdate(Cancel As Integer)

On Error GoTo e:

Me!Expiry = DateAdd("m", 6, DateValue(Me!Date) - 1)
Exit Sub

e:
MsgBox "The value you have entered is not a correct date value" & Chr(10) & Chr(13) & "Please re-enter the date", vbOKOnly, "Error"
Cancel = True

End Sub

Additionally, you shouldn't be adding this to the Table as a value, this is just more data you don't need.
To get this value I would use an Unbound textbox for display purposes only.

When you come to need the value, simply create a query to find out which Dates have expired.

Imagine if someone decided that the expiry is 9 months? unlikely, but how would you deal with that?
Change all the values you have OR change 1 number in a query?

Regards,

 

[ansentry]

My penny's worth,

txtExpiryDate should be an unbound text box, (the result should not be saved into a table)

Private Sub StartDate_AfterUpdate()

Me!txtExpiryDate = DateAdd("m", 6, DateValue(Me!txtStartDate) - 1)

End Sub

Also (another penny's worth) you will notice that I have put txt before the names of the text boxes, I would suggest that you alway do this so that access knows which is the data and which is the control. So to make my code work just make the name of the Start Date txtStartDate. Same thing with the Expiry Date

 

[miss_sunshine]

Thanks for your help. So far the query itself works, but the text box does not update automatically yet.

I haven't tried making the text box unbound, because I need the data to be saved to the table - I have a query that looks up expiry dates in the next 14 days.

Is there another way around this?

 

[Ian Mac]

Thanks for your help. So far the query itself works, but the text box does not update automatically yet.

I haven't tried making the text box unbound, because I need the data to be saved to the table - I have a query that looks up expiry dates in the next 14 days.

Is there another way around this?

As I have mentioned you don't want to be storing the value:

Additionally, you shouldn't be adding this to the Table as a value, this is just more data you don't need.
To get this value I would use an Unbound textbox for display purposes only.

When you come to need the value, simply create a query to find out which Dates have expired.

Imagine if someone decided that the expiry is 9 months? unlikely, but how would you deal with that?
Change all the values you have OR change 1 number in a query?

If you need more pointers please ask, but don't store the value, it's not needed, ANYWHERE in a table.

 

[Ian Mac]

Please see attched, no code just a Query, should be what you are looking for as a basic.

The records get saved but not the ExpiryDate, this generated every time.