Date Calculations

[MrMcFestoe]

I need to find a way to do the following, on a form i have a text box LAST SERVICE DATE and a field SERVICE FREQ which is a combo and can equal 12,6,3 months what iam trying to do is create a Field on my form that also is in my main table called NEXT SERVICE DUE this needs to be the sum of SERVICE FREQ (which is in months) and LAST SERVICE DATE (ddmmyyyy).

I have tried different ways but they all seem to add 12,6,3 days on to the date, is there a way of only doing a calculation on the month bit of the date?

Thanks

 

[KenReay]

Hi

You need DateAdd() function

NextServiceDate = DateAdd("m",LastService,ServiceFrequency)


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MrMcFestoe]

Ken,

Thanks for the reply, with that info got it working, with the control source set to

= DateAdd("m",[Service Freq],[Last Service Visit])

Now i have a #Error showing up on every field where there is no date in the Last Service Visit field is there a way to deal with this? so if there are no dates to do the calculation on the field is left blank.

Thanks

 

[MrMcFestoe]

Ken,

After i posted the above i just looked at something else the value that the above is calculating is not being store in my main table even thought it is show on my main form.

Can the above calculation work and store the value in a table?

Thanks once again

 

[KenReay]

Hi
Assuming you have [Next Service Due] on your form

In the on current event of teh form put

If IsNull([Last Service Visit]) Then
' here do whatever you want to do if there is no last service visit date
Else
[Last Service Visit] = DateAdd("m",[Service Freq],[Last Service Visit])
End If

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MrMcFestoe]

Ken

Thanks for the replie, tried the code and it works ok, but if the Service Freq field is change then you have to go out of the record and back into it for the Next Service Due field to change, is there a way of this code running if any field is altered.

I might be wrong but could it be set in the OnTimer event (not got a clue)

Thanks

 

[SQLBI]

MrMcFestoe,

To show the changes on your form, within the control change event include the following.

Form_MyForm.ReQuery

This should update the form to include changes to the current record.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[KenReay]

hi

In the before update event of the form put the same code eg

If IsNull([Last Service Visit]) Then
' here do whatever you want to do if there is no last service visit date
Else
[Last Service Visit] = DateAdd("m",[Service Freq],[Last Service Visit])
End If

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK