Dates

[Ian Stow]

I have 3 fields in a form called Jobdate,Invdate & Reccashbydate. If the 1st date is input by me as say 13/01/2002 (but could be any day in the month) is there a calculation that will automatically input the Invdate as the 1st of the following month and Reccashbydate as the 1st of the month following the Invdate field.


Thanks in advance

 

[antomack]

The following should do the job.

Invdate: DateSerial(Year([Jobdate]), Month([Jobdate])+1, 1)
Creates a date of 1st of the month following date entered.

Reccashbydate: DateSerial(Year([Jobdate]), Month([Jobdate])+2, 1)
Creates a date of first of month, 2 months after date entered.

Need to set the two fields in the AfterUpdate event of Jobdate, and should set the other two dates automatically.

 

[Ian Stow]

Brilliant Thanks

But sorry for being stupid but I dont understand your last line.........Need to set the two........

 

[antomack]

How you set the values of the InvDate and RecCashByDate depends on how your form is set up. If your form is bound to a table then you need to use code behind the AfterUpdate event on the JobDate to set them, see below. If the form is unbound then you can use the following statements to set the values of the other two fields, each time that JobDate changes they will automatically change when you move focus from the JobDate field, in this case no details are being saved to a table unless you write code to do the save.

Bound Form
Insert the following code behind the form in the AfterUpdate event for JobDate.

Private Sub jobdate_AfterUpdate()

' check if job date is null
If IsNull(Me!jobdate) Then
Me!invdate = Null
Me!reccashbydate = Null
Else
Me!invdate = DateSerial(Year([jobdate]), Month([jobdate]) + 1, 1)
Me!reccashbydate = DateSerial(Year([jobdate]), Month([jobdate]) + 2, 1)
End If

End Sub


Unbound Form
Insert the following in the Control Source of the relevant fields, Control Source is on the field properties on the form.
InvDate:
=IIf(IsNull([jobdate]),Null,DateSerial(Year([Jobdate]),Month([Jobdate])+1,1))
RecCashByDate:
=IIf(IsNull([jobdate]),Null,DateSerial(Year([Jobdate]),Month([Jobdate])+2,1))

HTH